Saved from http://groups.google.com/groups?hl=en&th=386f4bc35aad6c97&rnum=1 with minor change (SID1,2,3 changed to TX1,2,3) From: Yong Huang (yong321@yahoo.com) Subject: Can setting delayed_logging_block_cleanout to false turn off delayed block cleanout? Newsgroups: comp.databases.oracle.server Date: 2001-12-23 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, TX1 does a fast commit leaving behind row level locks, among other things (for a complete list, see James Morle's "Scaling Oracle8i" p.223). At time B, because of a query e.g., TX2 cleans out the blocks TX1 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 "Practical Oracle8i", p.11). Since TX2'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. If set to false, this redo is written to logfiles. If the redo is not written to logfiles, then when TX3 comes to this block and makes some change to the block (not just querying), TX3 calls LGWR to write TX2's redo entries to logfiles along with TX3's own redo entries. Beginning with 8i, TX2'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, which is not true after the database has run for a while; this may be the reason Oracle calls 7.3 and on commit "fast" commit. Yong Huang yong321@yahoo.com From: Jonathan Lewis (jonathan@jlcomp.demon.co.uk) Subject: 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