RE: Fwd: Snapshot too old from READ-ONLY table (data pump export)

From: <rajendra.pande_at_ubs.com>
Date: Tue, 19 Jul 2011 09:12:16 -0400
Message-ID: <D4C8B99EB96F2C42B4E19A3B87664F5E01D384A3_at_NSTMC612PEX.ubsamericas.net>

I would also be interested to know if rowdependencies changes this in anyway

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laimutis.Nedzinskas_at_seb.lt
Sent: Tuesday, July 19, 2011 7:05 AM
To: oracle-l_at_freelists.org
Subject: Re: Fwd: Snapshot too old from READ-ONLY table (data pump export)

>http://arup.blogspot.com/2011/01/more-on-interested-transaction-lists.h
tml

Exactly.
But I had to add this to

http://avdeo.com/2008/06/16/interested-transaction-list-itl/

to

https://richardfoote.wordpress.com/2010/07/20/index-block-dump-block-hea der-part-i-editions-of-you/

and

https://richardfoote.wordpress.com/2010/07/28/index-block-dump-block-hea der-part-ii-and-read-consistency-i-cant-read/

to Jonatahn Lewis comments at:.

http://forums.oracle.com/forums/thread.jspa?messageID=3725660

Jonathan is probably the first in the above mentioned links to talk about
COMMIT scn not just CHANGE scn - who cares about change SCN anyway ? But he makes one interesting statement:

"When all the ITL entries show SCNs no later than your query SCN the clone
is read-consistent and the data can be used by the query."

I'd like to know what was ment by that. My interpretation is:

one can reconstruct a consistent read of a particular row at query start SCN. Or one can reconstruct a consistent read of the whole block at query
start SCN.

As oracle operates "in blocks" then I assume this is how oracle reconstructs the consistent read of a block: ALL ITL's have to be delt with
to have every row in a block rollbacked to commit SCN just before query start SCN.

and finally

http://forums.oracle.com/forums/thread.jspa?threadID=521913

Mark J Bobak tells what is really needed to complete the picture:

"Then, at the row level, in the row directory, there's a lock byte. The value of the lock byte will be either 0, if there is no lock on the row, or
it will be the number of the slot in the ITL that corresponds to the transaction that's making a change, or just holding a lock, on that particular row. So, the locked row in the block points to the ITL slot, and
the ITL slot points back at the undo segment/slot/sequence. "

Here, IMHO, the structure is complete:

Row Lock byte points to ITL, ITL points to transaction commit SCN and undo
record.

Then everything adds up: row level locking, transaction isolation, all ITL's traversal.

The block oriented nature of oracle is explained by Tom Kyte:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:273307 70500351
"o we cache blocks
o we read blocks
o blocks have transaction tables on them, this is what drives the multi-versioning

relational databases tend to do things "to blocks", it is basically the way
they work.

We do not multi-version ROWS.
We multi-version BLOCKS.
"

Quite a list to have somehow complete picture :) , isn't it ?

Brgds, Laimis N

---

---

Please consider the environment before printing this e-mail  

  From: Ron Chennells <ron.chennells_at_parkwestit.com>  

  To: oracle-l_at_freelists.org  

  Date: 2011.07.19 11:58  

  Subject: Re: Fwd: Snapshot too old from READ-ONLY table (data pump export)  

Some details here

http://arup.blogspot.com/2011/01/more-on-interested-transaction-lists.ht ml

Ron

Quoting Laimutis.Nedzinskas_at_seb.lt:

> Since the question was raised again, does anyone know the latest,
> up-do-date explanation on how oracle multi versioning works.
>
> ITL's, commit SCN's, row lock byte all put together and explained :)
>
> The problem is that many very clever people reverse engineered one or
> another aspect of the process but one can hardly find an all-out
> explanation.
> I dare to claim that some statements one finds on internet made by
even
> experienced oracle gurus are doubtful to say the least.
> Actually, oracle's own metalink is known to provide hmmm.. strange
> statements. That happens.
> The whole multi versioning is easy to explain at a high level but
> implementation is quite tricky.
>
> Why is implementation that important? Because knowing how it works one
can
> answer some questions right away how fast oracle can perform in
particular
> situations.
> It's less of a black box approach and more of algorithm analysis then.
>
>
> Thank you in advance,
> Laimis N
>
> --
> http://www.freelists.org/webpage/oracle-l:
>
>
>
>

  http://www.linkedin.com/in/ronchennells

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





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


Please visit our website at 
http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html 
for important disclosures and information about our e-mail 
policies. For your protection, please do not transmit orders 
or instructions by e-mail or include account numbers, Social 
Security numbers, credit card numbers, passwords, or other 
personal information.
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 19 2011 - 08:12:16 CDT