| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: delayed logging block cleanout -- Any other comments?
It's a long time ago, but I remember when I first read Steve's note I didn't think it was complete. My tests had suggested that
On "commit cleanout" an in-memory block could have its ITL updated with the SCN, and this buffer would then be dirty and get written out. The redo for the cleanout was NOT logged, however, so the block on disc was in a state that was NOT described by the redo.
The next transaction to update to that block would generate UNDO that made it look as if the block had previously been cleaned out, so that if the next transaction rolled back, the block would be left in a state that looked as if the previous transaction had done a full clean out. At no stage would there be any REDO generated that would otherwise move the block from 'not cleaned' to 'clean'. (Hence "delayed logging" is a euphemism for "never logged").
Going back to the beginning - if on the commit, the block was not available in memory, then it would not be cleaned at all. On the next read into memory (whether for query only or for update) the block would be cleaned. If the read was query only, the redo generated would be the full cleanout redo, and the block would be dirtied. If the read was for update, the UNDO for the update would be as in the first example - the UNDO that would take the block back to a clean state that had never actually existed.
I don't have an old version to repeat the tests on - but perhaps the change in your results is related to the size of the buffer, and the size of the update which would have some effect on how many blocks would be subject to commit-time partial cleaning, and how many blocks would still be in memory in and cleanable anyway.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
Has anybody tested this in an Oracle 7/8.0 database?
I have tested this yesterday and found incompatible results according to Steve's comments:
"If delayed_logging_block_cleanouts is TRUE, which is the
default in 7.3 and 8.0, the logging of this redo is delayed until another
change is made to the current buffer. The cleanout itself does not mark the
buffer as dirty or log the redo for its changes. The next change to the
buffer marks the buffer as dirty and logs redo for the cleanout as well as
its own changes.
" (from http://www.ixora.com.au/q+a/cr.htm)
In an Oracle 7.3.4 instance (d_b_c_o = TRUE) , whenever I execute a query against some table with uncleaned blocks, they get clean out !! (I can even see redo being generated by the session execting the query in v$sesstat.)
I had performed the same test a couple of years ago against another O7 instance and it worked out just like Steve says above, I don't know what I'm doing wrong now... , may this behavior be influenced by a port specific issue? (different OS or something like that?..)
Any ideas?
Thanks
Diego.
 |
 |