Re: How are ITL entries banked?

It's been a few years since I looked at ITL entries in depth. I may not get the details exact, but hopefully close enough to answer your question.

ITL entries are also called ITE (Interested Transaction Entry). ITEs are written as part of the transaction. There is a flag that indicates if a transaction is uncommitted or commited. The scn saved is either the transaction start or transaction commit (depending on the transaction state). An incoming read uses this scn to determine if read consistency or block cleanout is required.

When a transaction identifies a row in a block to be locked, it first creates undo entries for the current state of the ITL and the row to be locked. This allows the undo applied to not only return the row to the pre-transaction/pre-commit state, but also the ITL. This enables multiple undo operations on the same row in case several transactions have modified the row since the query started.

A transaction will not 'blank' out an ITE after commit. An existing query may have started before the commit and the ITE information is crucial to read consistency.

Block Cleanout occurs when a query or transaction finds data that was part of a committed transaction, but the ITE was not updated to indicate this commit. This often happens when a large transaction occurs and the modified blocks are aged out of the cache before the commit occurs. Oracle does not read the blocks back into the cache to change the ITE. When another query/transaction finds an old ITE, it looks for the indicated undo, but cannot find it (a bi-directional sanity check occurs to validate that the ITE undo and the existing undo at the address are the same). Since undo for an uncommitted transaction is NEVER OVERWRITTEN (absent an Oracle bug...and I've never heard of one that causes this), the process knows that these changes have been committed, so the ITE is indicated as committed.

I hope this answers your questions.

Regards,
Daniel

-- 
Daniel Fink

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com

Lost Data?    http://www.ora600.be/


Martin Klier wrote:


> Dear listers,

>

> I know only a bit, how the ITL in a block works.

>

> One question arises now: How are ITL entries blanked out again? Are they

> blanked at all?

>

> Two theories:

> 1.) A DML operation inserts its XID, UBA, SCN and the ROWID it works on,

> into my block's ITL. On commit, this entry is blanked out.

> BUT it means, that on commit, all involved blocks are subject to another

> touch, even a readback from disk is possible!

>

> 2.) A DML operation inserts its XID, UBA, SCN and the ROWID it works on,

> into my block's ITL. On commit, the XID is closed, and so the ITL of my

> block contains an old XID's entry. Thus there is no need to blank it out.

> BUT now, the next DML operation coming over the block, has to check each

> ITL entry sitting around, and ask the core system if the XID mentioned in

> the ITL entry is closed or not. Okay, the SCN will be older (lower) than

> the current DML in question, but this circumstance does not allow the

> conclusion that the operation causing the (old) entry is already closed.

>

> I guess, there's a third way I wasn't able to see. Can you help me

> understanding Oracle transactions a bit better?

> Thanks a lot in advance!

> --

> Mit freundlichem Gruß

>

>

> Martin Klier

> Senior Oracle Database Administrator

> ------------------------------------------------------------------------------

>

> Klug GmbH integrierte Systeme

> Lindenweg 13, D-92552 Teunz

> Tel.:  +49 9671/9216-245

> Fax.: +49 9671/9216-112

> mailto: martin.klier_at_klug-is.de

> www.klug-is.de

> ------------------------------------------------------------------------------

>

> Geschäftsführer: Johann Klug, Roman Sorgenfrei

> Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,

> HRB Nr. 2037, Amtsgericht Amberg

>

> --

> http://www.freelists.org/webpage/oracle-l

>

>

>

>   


--
http://www.freelists.org/webpage/oracle-l