Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, NagaKrishna.

Asked: January 10, 2017 - 4:35 am UTC

Last updated: March 11, 2017 - 2:40 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Thanks for providing answers in detail for us

Though you explained in so many times still I have confusion about this topic,can you clear my question.



"A query that reads datablocks that have been updated by a large DML (INSERT/UPDATE/DELETE) may well incur "delayed block cleanout". The commit operation of the DML does not involve clearing the ITL entries in each modified datablock but only marking the transaction in the undo slot entry as "COMMITTED" (plus, possibly, a few blocks being updated). Therefore, the next query (whether from the same database session OR from another database session) to read the datablock has to verify the ITL entries against the transaction in the undo segment and then "clean out" the ITL entry and the row marker in each datablock"

if committed data in undo segment got overwritten ,then how come next query perform delayed block clean out without details about committed change?





and Connor said...

Here is a really good thread on this

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:811822300346967203

But think about it this way - I'm reading a block and because it is not cleaned ou, it *looks* has a really really old uncommitted transaction on it.

Now if that transaction is actually still active, then the actual undo information will still be there - obviously we will *never* throwaway undo information for a transaction that is open (and hence might still want to rollback).

So if using the itl information on the block, I go lookup the undo information ... and find that its done, or has been overwritten, then I know by definition that this transaction must have committed a long time ago and hence the block can be cleaned.

Rating

  (3 ratings)

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

Comments

A reader, January 10, 2017 - 5:24 am UTC

Thanks for the quick response.

A reader, January 10, 2017 - 6:13 am UTC


ORA-01555 - Unable to termine commit SCN

A reader, March 09, 2017 - 8:57 am UTC

Hi Tom,

In your book《Expert Oracle Database Architecture Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions》 on page 341 you said the following：

“The problem is, however, that our query is unable to determine in this particular case if the
COMMIT SCN of the block is greater than or less than t1. It is unsure as to whether it can use that block
image or not. The ORA-01555 error then results”

I have 3 questions:
1. How does Oracle know the transaction slot is overwritten?

2. Since the slot is overwritten, which happens after t1, COMMIT SCN of that slot should be greater than t1. Why can't it determine which one is greater?

3. In https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923,
you said if the transaction table is overwritten and system cannot rollback the rollback segment transaction table sufficiently, it will return ORA-1555 since Oracle can no longer derive the required version of the data block. But in your book, you mentioned that query is unable to determine if the COMMIT SCN of the block is greater than or less than t1? So I get confused with the 2 analysis above.



Thanks so much
Connor McDonald
March 11, 2017 - 2:40 am UTC 

SQL> desc v$transaction
 Name
 -------------------------
 ADDR
 XIDUSN
 XIDSLOT
 XIDSQN
  ...


Associated with a transaction is the undo segment (xidusn), the slot (xidslot) but also a sequence number (xidsqn).

So in the example you're referring to, I'll head off to undo segment for a particular slot and sequence #. But when its been overwritten, the sequence # I find will have moved on.

Because it got overwritten, I *know* the transaction has committed, but what I've lost access to is the time (scn) at which it committed, so I dont know whether I should have undone or not for a consistent read.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.