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'.
<[EMAIL PROTECTED]
net> To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]>
> Date: 2003/08/06 Wed PM 11:29:23 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
