Skip to Main Content
  • Questions
  • When is a commited transaction removed form the ITL

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Yousef .

Asked: April 28, 2008 - 6:59 am UTC

Last updated: March 21, 2011 - 12:01 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi Tom,
A transacion entry is created in the ITL in the data/index block header when a transaction modifies a block. How long does this entry stays in the ITL after the transaction is committed and do you get a snapshoot too old if a long runnig query accessed the block after the transaction entry was removed?

Regards
Yousef Rifai

and Tom said...

it could stay there forever.

It never needs to be removed.


The ITL points to the undo information. If the undo information is not there anymore because lots of time has passed - we know that transaction is long long gone.

what you might be asking about is something to do with delayed block cleanouts

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:895410916429

There, the problem would be that a recent transaction (not from a while ago, but just happened a short time ago) leaves itself behind in the block header and commits - and then a long running query begins and eventually hits this block. Now the undo that could roll back this block is gone for whatever reason and the transaction that modified it wasn't "old enough to be known for sure to have predated the query" (they started pretty close to each other in time). That will result in a 1555 since we cannot tell if the block image we are reading is old enough or not.

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Still a bit confused.

Yousef Rifai, April 29, 2008 - 2:13 pm UTC

Thank you for your prompt response. I shall give an example:
Long query started at SCN 100
20 transactions modified block B1 and commited in sequence. The first (T1) committed at SCN 200 and the last at SCN 3,000. During the process the entry for the transaction T1 was over written in the ITL.
At SCN 3,500 the query read Block B1. The entry for T1 is no longer there but it is needed to rollback B1. What happens, do we get 1555 if not how does oracle rolls back the transaction.
Regards
Yousef Rifai
Tom Kyte
April 30, 2008 - 8:25 am UTC

... During the process the entry
for the transaction T1 was over written in the ITL.
...


does not matter if the undo for the first transaction still exists because....

we get the block, it is AS OF SCN 3,000. We roll it back - that can restore the transaction information as well. We keep doing that - so that the overwritten transaction information you had would "come back".

Delayed Block Cleanout

Brian B, April 29, 2008 - 4:22 pm UTC

"That will result in a 1555 since we cannot tell if the block image we are reading is old enough or not. "

Can you elaborate on that more? That's the part of DBC I've never completely understood. At some point in time, reads from that block will no longer raise an ORA-01555, right? How can we determine that point?

For example, here is one situation I run into and can never quite understand.

Monday - import data into Database.
Undo retention set to 8 hours (longer than all known queires or exports). Import finishes and some queries are run to validate, but most data is untouched.
Tuesday - no activity.
Wednesday - undo retention has long since past. Data is exported, but raises ORA-01555 due to DBC on many tables which were imported, but not queried since.


Tom Kyte
April 30, 2008 - 9:19 am UTC

when you start your query, we know a couple of things (think conceptually here, this is sort of how it works)


a) Transaction information such that any transaction that is known to predate a certain time - is safely committed. So, if you encounter a transaction older that - we know it predates our consistent read time.

b) what 'scn' your query must be as of, we'll only read data committed by transactions that predate your transaction.


So, we get to a block. It has transaction information on it. We look at the block header and ask "was the last thing done on this block and committed before we started?" - We can sometimes answer that from the information in (a).

With a delayed block cleanout and the assumption is that right before we started our query, a transaction committed a change to a block we will eventually read - we cannot answer that based on the stuff in (a)

Now, it is not likely that the transaction will be known to be "old" when we start - since it just finished.

So, we attempt to find the undo information for this transaction - but since undo was overwritten - we cannot.

So, we fail with the 1555. You restart the query. This time the old transaction will fall into the (a) category and the query succeeds.

Remember, time is not wall clock time in Oracle - a "short time" above could be a long time from the perspective of us humans.



In your example, the undo retention is set to 8hours, but did you allocate 8 hours worth of undo space - you might have a lot less than 8 hours of undo there (v$undostat will tell you if you prematurely expire stuff)

Excellent Answer

Yousef Rifai, April 30, 2008 - 10:13 am UTC

Many THANKS Tom for an excellent and easy to understand answer.

Best Regards
Yousef Rifai

Delayed Block Cleanout

Brian B, April 30, 2008 - 11:07 am UTC

Thanks - that helps.

I found and am trying the suggestions from this thread
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:895410916429#41381017568925
to see if that helps avoid the 1555 in this case.


Some confusion

jiang huang zheng, June 01, 2008 - 2:54 am UTC

Hello Tom

Appreciated if you would take some time looking at this quoted from your comments above:
<Quote>
when you start your query, we know a couple of things (think conceptually here, this is sort of how it works)


a) Transaction information such that any transaction that is known to predate a certain time - is safely committed. So, if you encounter a transaction older that - we know it predates our consistent read time.

b) what 'scn' your query must be as of, we'll only read data committed by transactions that predate your transaction.

</Quote>
I understand what 'b' means , and have some trouble getting through what 'a' is saying, can you elaborate a little more about it?

Thanks!
Tom Kyte
June 02, 2008 - 7:49 am UTC

blocks do not have SCN's on them. They have transaction information in the header. Using that information - we can many times go to the undo segments (using the pointers in the block header) to figure out "what time this block was last modified" - the SCN.

Well, sometimes - we cannot do that - sometimes the information in the undo segment is no longer present. So, sometimes we hit a block that we cannot get the 'scn' for.

So, if the transaction was old enough - older then the oldest transaction we knew of when we started - we know that block has not been modified in a long time - it is "good", we can use it, we don't need to roll it back - we know it was not modified since our query began


The problem I was describing was one when we raise a 1555 because 1) the undo information isn't there any more, it got wiped out while we were executing our query and 2) the transaction isn't old enough for us to "know" it predates us.


See the link in the original example, it demonstrates getting a 1555 error on a readonly tablespace. It demonstrates the edge case I was describing, the case whereby this issue arises.

delayed block cleanouts question

John, December 24, 2008 - 10:26 am UTC

Tom,

I am having hard time understanding following comments from you:

1) "Well, sometimes - we cannot do that - sometimes the information in the undo segment is no longer present. So, sometimes we hit a block that we cannot get the 'scn' for."

2) "So, if the transaction was old enough - older then the oldest transaction we knew of when we started - we know that block has not been modified in a long time - it is "good", we can use it, we don't need to roll it back - we know it was not modified since our query began
"

In 1), "hit a block that we cannot get the 'scn' for", are you refering to block in Undo/Rollback segment?

In 2), "if the transaction was old enough", which transaction are you refering to? If you did not get the SCN from 1), how can Oracle know transaction was old enough? Once Oracle determines that data is good, does Oracle change data block from "uncommitted" to "none"?

Thank you very much.
Tom Kyte
December 29, 2008 - 3:27 pm UTC

in 1) no, I'm referring to a block in the table, the undo history for it could be gone - we don't know if it is "old enough" at that point.


in 2) the one running the query. When you get that 1555 and restart - we'll know now that "of course that block must be old enough". We now know a point - an age - a time - that before this - all changes are OK to see.

Delayed Block Cleanout

A reader, March 21, 2011 - 9:57 am UTC

Tom:

I have a big update going on in chunks with commits in between every 10000 records. While this update is going on I have another session trying to query the same table which is getting updated. This second session waits are on "db file sequential read" on undo datafile blocks. This session usually gets done with in 5 min if there are no waits on "UNDO" blocks.

How am I going to handle the delayed block clean outs?

Regards and Thanks in Advance
Tom Kyte
March 21, 2011 - 10:16 am UTC

You are not necessarily seeing delayed block clean outs here, you are seeing normal "what happens when my buffer cache cannot hold my working set of data".

Your cache is getting full, we are forced to write blocks to disk, some of those blocks are your undo blocks - needed for normal, good old read consistency.

The delayed block cleanout would just be reading the undo segment header - which should be so hot it would be in the cache - and then modifying the block it already has. It is not the culprit here - just a "too small of a cache for the amount of blocks I need to cache"

Previous review

A reader, March 21, 2011 - 10:31 am UTC

Tom:

My table is part of the recycle pool. The indices are not. They are part of the default pool. Do you think this might be one of the reason for my problem? Or Should I increase my default pool size to address this problem?

Thanks a Ton,
Tom Kyte
March 21, 2011 - 12:01 pm UTC

Your waits are on the default pool, aren't they - you said they were for undo blocks right?

Use the buffer cache advisor (statspack, awr reports) and see what it says, it'll can give you guidance on what would happen if you increased the size of your default pool - as far as reduced physical IO goes.