Can setting delayed_logging_block_cleanout to false turn off delayed block cleanout?

The parameter delayed_logging_block_cleanout is only valid in 7.3 through 8.0.6. But I find a lot of confusion everywhere. My understanding of Steve Adams's words (www.ixora.com.au/q+a/cr.htm, 22 September 1999 and 2 February 2000 Q&As) is that delayed block cleanout can never be turned off. The parameter delayed_logging_block_cleanout if set to false only turns off redo logging (i.e. LGWR writing redo entries to logfiles) when the cleanout is done.

Here's an example. At time A, SID1 does a fast commit leaving behind row level locks, among other things (for a complete list, see James Morle's book p.223 or p.232 I don't remember which). At time B, because of a query e.g., SID2 cleans out the blocks SID1 committed transactions on, if these blocks haven't been written by DBWn yet. This delayed block cleanout can't be turned off and this feature has existed since pre-7.3 (see Jonathan Lewis's book, p.11). Since SID2's cleanout modifies current mode blocks (again for details of this modification see J. Morle's book), redo is generated. Now it's the parameter delayed_logging_block_cleanout that comes into play. If left alone or set to true, this redo is only recorded/logged to log buffer but not written to logfiles (am I right?). If set to false, this redo is written to logfiles. If the redo is not written to logfiles, then when SID3 comes to this block and makes some change to the block (not just querying), SID3 calls LGWR to write SID2's redo entries to logfiles along with SID3's own redo entries. Beginning with 8i, SID2's redo is always immediately written to logfiles, equivalent to setting that parameter to false.

Oracle documentation about this parameter seems to be wrong
(technet.oracle.com/doc/server.804/a58242/toc.htm):
"DELAYED_LOGGING_BLOCK_CLEANOUTS turns the delayed block cleanout feature on or off". I think it's missing one important word "logging". But later on it's correct to have this word: "no logging block cleanout at commit time. Logging the block cleanout occurs at..." Because of this documentation error, many people have wrong understanding, including the book Oracle 24X7 Tips and Techniques
(according to my impression last time I read it).

There's also confusion about fast commit, a.k.a. commit cleanout, depending on what aspect of it you emphasize. This feature was also introduced at 7.3. It can never be turned off. Setting delayed_logging_block_cleanout has no effect on fast commit. BTW, this is called fast commit but it's actually slower commit than the pre-7.3 commit, which "didn't mark any of the changed data blocks" on commit while 7.3 and on databases at least record the SCN in the block (J. Lewis's book pp.11-12). Obviously for pre-7.3. commit to be faster, it has to have no previous cleanout work to do for this comparison; this may be the reason Oracle calls 7.3 and on commit "fast" commit.

Correct me if any of my understanding is wrong. I didn't do any lab test because I'm not that good at reading block dumps and I don't have access to a Version 8 or 7 database to play at my will.

Yong Huang
yong321_at_yahoo.com Received on Fri Dec 21 2001 - 13:49:57 CST