Still a bit confused.
April 29, 2008 - 2pm Central time zone
Reviewer: Yousef Rifai from UK
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
Followup April 30, 2008 - 8am Central time zone:
... 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
April 29, 2008 - 4pm Central time zone
Reviewer: Brian B from Columbus, OH
"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.
Followup April 30, 2008 - 9am Central time zone:
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
April 30, 2008 - 10am Central time zone
Reviewer: Yousef Rifai from UK
Many THANKS Tom for an excellent and easy to understand answer.
Best Regards
Yousef Rifai
Delayed Block Cleanout
April 30, 2008 - 11am Central time zone
Reviewer: Brian B from Columbus, OH
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
June 1, 2008 - 2am Central time zone
Reviewer: jiang huang zheng from China
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!
Followup June 2, 2008 - 7am Central time zone:
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
December 24, 2008 - 10am Central time zone
Reviewer: John from VA, USA
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.
Followup December 29, 2008 - 3pm Central time zone:
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
March 21, 2011 - 9am Central time zone
Reviewer: A reader
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
Followup March 21, 2011 - 10am Central time zone:
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
March 21, 2011 - 10am Central time zone
Reviewer: A reader
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,
Followup March 21, 2011 - 12pm Central time zone:
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.
|