Re: Can setting delayed_logging_block_cleanout to false turn off delayed block cleanout?

The problem with asking questions that
reference 8.0.6 and 7.3 is that I don't have an installed copy any more.

Reading the various references you have listed, I can see that there are little gaps, and even contradictions, between the sources. Indeed, reading Steve's 1999 notes in particular, it crossed my mind that I should have done a couple more experiments to clarify my understanding of what was going on at that time under those versions.

The answer your question is "no". The only thing that stops delayed block cleanout is turning the containing tablespace read-only. Oracle MUST know for read-consistency purposes, something about when a transaction that is no longer running actually committed - and delayed block cleanout deals with that issue.

The problem with delayed_logging_block_cleanout is that Oracle has not (to my knowledge) ever produced a note which says: 'this is how a commit works, and this particular aspect of this specific phenomenon is called delayed_logging_block_cleanout'. This means that Steve, James, I, and others can observe features of the commit and subsequent cleanout activities and say "ah! that bit must be the 'delayed logging' bit". Life is not made simpler by the fact that details of the mechanism change over time anyway.

To me, the thing that is the 'delayed logging' is this:

When you commit, the commit cleanout updates some of the changed data blocks by stamping them with a couple of SCNs. But the commit still takes only 60 bytes of redo - the changes to the data blocks are not logged i.e. they are delayed.

When the block is subsequently updated by another transaction, the 'full' cleanout of the ITL and its row locks takes place. However, if you examine the redo generated at this point, you will find that it has been engineered to look as if the new update started from a fully cleaned out block. In this respect, the logging of the commit cleanout has not been delayed, it has been totally bypassed.

Of course, the last two paragraphs explain what happened under 8.1.5. There are places between the commit cleanout and the end of the next transaction where specific details (e.g. is the block marked as dirty are subject to re-write after the commit cleanout) may have changed between 7.3 and 9.0.1.. Also, the bits affected by the delayed_logging_block_cleanout parameter may not have been consistent across all versions.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Yong Huang wrote in message ...


>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