This discussion is archived
10 Replies Latest reply: Jan 25, 2013 2:05 AM by Jonathan Lewis RSS

Delayed block cleanout when tablespace is read only.

698658 Newbie
Currently Being Moderated
Hi,
just want some clarification about delayed block mechanism when tablespace is put read only immediately after huge update of one of the table .
How recent Oracle version deals with that ?
What if after lets say month of inactivity I query such table and Oracle notice some blocks needs cleanout ?
How to check transaction table after such long time ?:)
Regards
GregG
  • 1. Re: Delayed block cleanout when tablespace is read only.
    karan Pro
    Currently Being Moderated
    In the case you mentioned that after a long long time later lets say a session visits the block and there is an uncommitted change in the block header information, oracle goes to rollback segmment transaction table slot isnt it ? sees that it has been committed and then will change the block to update the new information in the original block header.

    Offcourse it will not hold undo information for so so long because committed transactions are meant to be overwritten once they expire, so there can offcourse be a case when rollback is overwritten and also rollback transaction slot is overwritten, lets say there is a user A who issues a select query at scn 100 and sees that the blocks it is reading have changed after it fired select and this has not been cleaned out right ? (delayed block cleanout), now this user A session must check whether the block it is reading have been changed after scn 100, for this oracle sees rollback segment transaction table slot to determine the committed SCN, if it is higher than 100 like in my above case oracle needs a consistent old image and if it is before 100 then it just gets cleaned out. Now the point is that if the transaction slot has been overwritten and the transaction table cannot be rolled back to a sufficiently old enough version then Oracle cannot derive the block image and will return ORA-1555.

    There was a retention guarantee mechanism introduced in 10g to make sure commiitted transaction will not be overwritten till the time undo_retention time gets over, because by default it can get overwritten if there is space pressure on undo even if undo_retention time has not expired.
  • 2. Re: Delayed block cleanout when tablespace is read only.
    JohnWatson Guru
    Currently Being Moderated
    .

    Edited by: JohnWatson on Jan 24, 2013 9:34 AM
    Sorry, hit the wrong button. Please ignore.
  • 3. Re: Delayed block cleanout when tablespace is read only.
    JohnWatson Guru
    Currently Being Moderated
    GregG wrote:
    What if after lets say month of inactivity I query such table and Oracle notice some blocks needs cleanout ?
    When a tablespace is made read-only, it is checkpointed. Therefore there will be no blocks that need clean out.
  • 4. Re: Delayed block cleanout when tablespace is read only.
    karan Pro
    Currently Being Moderated
    If there is a huge update also , buffers can be flushed to datafiles before the checkpoint(tablespace read only) as well.
  • 5. Re: Delayed block cleanout when tablespace is read only.
    698658 Newbie
    Currently Being Moderated
    Thank You, I think I've found something interesting from Mr Lewis :
    http://www.jlcomp.demon.co.uk/faq/ts_readonly.html

    Looks like those blocks are never cleaned out hence little overhead every read is included .
    Regards
    GregG
  • 6. Re: Delayed block cleanout when tablespace is read only.
    karan Pro
    Currently Being Moderated
    You'r welcome and thanks to you as well, the interesting thing about that post is the following

    The overhead is not huge – but there’s no point in paying it. So consider forcing a scan of all objects in a tablespace before switching it to read only mode to make Oracle clean out any blocks that need it. Note – however, that there are at least a couple of reasons why this might be a pointless use of resources. For example, any blocks that were in memory and not cleaned out when you switched the tablespace to read only mode will be cleaned before being written. Moreover, if the contents of the tablespace have been created through ‘create as select’ they will have been created as clean. If there are any other reasons, I can’t think of them right now.
  • 7. Re: Delayed block cleanout when tablespace is read only.
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    GregG wrote:
    Thank You, I think I've found something interesting from Mr Lewis :
    http://www.jlcomp.demon.co.uk/faq/ts_readonly.html

    Looks like those blocks are never cleaned out hence little overhead every read is included .
    Last update on that post 2007, and last version referenced 8.1.7 - so not really trustworthy as a definitive statement. ( http://jonathanlewis.wordpress.com/2006/11/13/trust/ ) But that bit is still true - you can get blocks on disc in a read-only tablespace which are notionally in need of delayed block cleanout, and can't be cleaned.

    I'm not sure how I got to the comment about blocks being cleaned before written the read-only if they were still in memory (I can't find the proof at present). It may be true, it may be a special or limited case.

    Depending on volume of data access the overheads on reading may not be so insignificant. I've just done a quick test (insert data with index, flush buffer cache, set ts readonly, access rows rows by unique key). The costs for each call to select a row were:

    a) 2 buffer gets on the index, one buffer get on the table - basic access requirement.
    b) 2 free buffer requests to create a CR copy of the leaf block and table block
    c) 2 db block changes (NOT reported in the session stats, but reported in the segstats)
    d) 2 calls to "get commit time" ktugct

    Depending on the type of activity, it could be worth trying to make the data clean before you make the tablespace read-only. In my case I was doing lots of single row access - but if you do lots of tablescans the overhead might be once per block rather than twice per row, which may be acceptable.

    11g also moves the goalposts because of its inclinaction to use direct reads anyway on tablescans.


    Regards
    Jonathan Lewis
  • 8. Re: Delayed block cleanout when tablespace is read only.
    698658 Newbie
    Currently Being Moderated
    Thanks Jonathan .
    Regards
    GregG
  • 9. Re: Delayed block cleanout when tablespace is read only.
    jgarry Guru
    Currently Being Moderated
    >
    11g also moves the goalposts because of its inclinaction to use direct reads anyway on tablescans.
    Inclinaction: There's a typo that ought to be a real word (if you didn't do it on purpose). Less passive than inclination because something actually happens.
  • 10. Re: Delayed block cleanout when tablespace is read only.
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    jgarry wrote:
    >
    11g also moves the goalposts because of its inclinaction to use direct reads anyway on tablescans.
    Inclinaction: There's a typo that ought to be a real word (if you didn't do it on purpose). Less passive than inclination because something actually happens.
    Just a typo - but it certainly sounds as if it ought to mean something. To my ear it sounds like an uncomfortable medical procedure.

    Regards
    Jonathan Lewis

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points