Re: How are ITL entries banked?
- From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
- To: Martin.Klier@xxxxxxxxxx
- Date: Fri, 08 Jan 2010 10:07:01 -0700
Martin,
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@xxxxxxxxxx
www.klug-is.de
------------------------------------------------------------------------------
Geschäftsführer: Johann Klug, Roman Sorgenfrei
Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
HRB Nr. 2037, Amtsgericht Amberg
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
