Re: RE: why does block cleanout incur redo?

Excerpts from one of my notes, collated from excellent papers or websites by Steve Adams, Jonathan Lewis, and KGopal.

The first block of every rollback segment, also called the segment header, has a structure called the transaction table, which keeps track of all the active transactions in that rollback segment. Transactions that use a rollback segment first register themselves in the transaction table using their transaction id. Also, each data block has one or many slots for an Interested transaction List, also called the ITL, in its header. Any transaction which modifies a row in a datablock must first get an ITL slot in that datablock. Each ITL consists of a transaction id, an address to the transaction table in the rollback segment, where the undo is located, a flag to indicate if the transaction is open, committed or pending for a cleanout, and the commit SCN. The ITL is contained in the transaction layer of an Oracle block header. Apart, from this, the cache layer of the oracle block header, also has an SCN which represents when the block was last changed.

When a transaction issues a commit, Oracle marks the transaction as commited in the transaction header table of the corresponding rollback segment. Also, the changes in the log buffer are written to disk. However, the transaction entry in the ITL of the data block may or may not be commited. Before Oracle 7.3, Oracle never marked any of the changed data blocks as commited. Any subsequent transaction that needs to read the changed block, would be directed to check the rollback segments to see if the transaction is commited, and would then update the block headers with the commit SCN, close the ITL, and would tidy up the data block by writing it to disk. This process is called a 'delayed block cleanout'. Staring with Oracle 7.3, Oracle introduced a fast commit mechanism, whereby it does mark some of the changes as commited, depending on if your transaction is a short one or a long one. The threshold value which decides if a transaction is a long one or a short one is 10% of the value for db_block_buffers. Also, the affected data blocks should be in the buffer cache. Thus, if the number of blocks that is modified by a transaction does not exceed 10% of the number of blocks in the database buffer cache, and all of affected data block are still in the buffer cache, Oracle will indeed perform a 'commit cleanout'. Else, the next transaction reading the affected data blocks will perform a 'delayed block cleanout'.

                                                                                                                   
                    <rgaffuri_at_cox.                                                                                 
                    net>                 To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    Sent by:             cc:                                                                       
                    ml-errors_at_fatc       Subject:     Re: RE: why does block cleanout incur redo?                  
                    ity.com                                                                                        
                                                                                                                   
                                                                                                                   
                    08/07/2003                                                                                     
                    07:24 AM                                                                                       
                    Please respond                                                                                 
                    to ORACLE-L                                                                                    
                                                                                                                   
                                                                                                                   

thanks... I have jonathan lewis's book on my list to read.

The transaction table is what is modified with the initrans and maxtrans setting. I get it now.

when you perform block clean out you modify the block and state that it is no longer being used in this transaction. when the blocks are flushed by DBWR, the transaction table needs to be modified so that the number of transactions that is occurring is known.

this causes redo. so that if you have to recover, the transaction table in each block can be accurate. Am I correct?
>
> From: "Cary Millsap" <cary.millsap_at_hotsos.com>
> Date: 2003/08/06 Wed PM 11:29:23 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: why does block cleanout incur redo?
>
> It's because "flush" doesn't mean what you probably think. During a
> delayed block cleanout, Oracle updates a block's transaction table
> (ITL). Any time a block gets modified, there's redo.
>
>
>
> See Jonathan Lewis's Practical Oracle8i (pp43-44) for a description.
>
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic <http://www.hotsos.com/training/clinic101> 101 in
> Denver, Sydney
> - Hotsos Symposium 2004 <http://www.hotsos.com/events/symposium/2004>
> March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
> -----Original Message-----
> Ryan
> Sent: Wednesday, August 06, 2003 9:34 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> My understanding of block cleanout is that oracle is flushing
> transaction information of already committed transactions from the
> buffer cache. This can happen in selects, when 10% of the buffer cache
> is filled with 'lists' if blocks involved in transactions, or with dml.
>
>
>
> i dont understand why this incurs redo? your just flushing blocks that
> are no longer needed?
>
>
>

It's because "flush" doesn't mean what you probably think. During a delayed block cleanout, Oracle updates a block's transaction table (ITL). Any time a block gets modified, there's redo.

See Jonathan Lewis's Practical Oracle8i (pp43?44) for a description.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Hotsos Clinic 101 in Denver, Sydney
- Hotsos Symposium 2004 March 7?10 Dallas
- Visit www.hotsos.com for schedule details...
     -----Original Message-----
     From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On Behalf
     Of Ryan
     Sent: Wednesday, August 06, 2003 9:34 PM
     To: Multiple recipients of list ORACLE-L
     Subject: why does block cleanout incur redo?

     My understanding of block cleanout is that oracle is flushing
     transaction information of already committed transactions from the
     buffer cache. This can happen in selects, when 10% of the buffer cache
     is filled with 'lists' if blocks involved in transactions, or with
     dml.

     i dont understand why this incurs redo? your just flushing blocks that
     are no longer needed?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Rajesh.Rao_at_jpmchase.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).