snapshot too old

[nk]

Hi
I was reading about ORA-01555 snapshot too old error.
It said -
This error can occur when the transaction that made the change already committed and :
*the tranaction slot in the rollback header has been reused
*the before-image in the rollback segment has been overswritten by another transaction.

I don't quite get it? Can somebody explain this using a tx example?

thank you!
nk

[shawish_sababa]

Hi

say, when you are performing a select and the data in the table, and the table is very big. on the other end, the table you are selecting from are changed by other users.
RBS is not one users privet garden, it is used be many users at the same time, and some thimes it gets over crauded. so what happens is that transactions that need new extents run other extents used by other users.
at specific point in time your long-running select tried to create a read-consistent view of some block that once was stored in RBS, but has allready been overwritten by some other transactions. So because your select was not able to read the whole dataset as it was at the moment the query started, it failed with ORA-1555.

Search in this forum for ORA-1555, or for "snapshot too old". there are at least dozen of threads with more detailed explanations why ORA-1555 happens.

also look at the oracle documentation on RBS.

[vicky]

Hi,

There are two fundamental causes of the error ORA-01555 that are a result of Oracle trying to attain a 'read consistent' image. These are :

1. The rollback information itself is overwritten so that Oracle is unable to rollback the (committed) transaction entries to attain a sufficiently old enough version of the block.

2. The transaction slot in the rollback segment's transaction table (stored in the rollback segment's header) is overwritten, and Oracle cannot rollback the transaction header sufficiently to derive the original rollback segment transaction slot.


CASE 1 - ROLLBACK OVERWRITTEN

Note :QENV (query environment) i.e. SCN
Steps:

1. Session 1 starts query at time T1 and QENV 50

2. Session 1 selects block B1 during this query

3. Session 1 updates the block at SCN 51

4. Session 1 does some other work that generates rollback information.

5. Session 1 commits the changes made in steps '3' and '4'.
(Now other transactions are free to overwrite this rollback information)

6. Session 1 revisits the same block B1 (perhaps for a different row).

Now, Oracle can see from the block's header that it has been changed and it is later than the required QENV (which was 50). Therefore we need to get an image of the block as of this QENV.

If an old enough version of the block can be found in the buffer cache then we will use this, otherwise we need to rollback the current block to generate another version of the block as at the required QENV.

It is under this condition that Oracle may not be able to get the required rollback information because Session 1's changes have generated rollback information that has overwritten it and returns the ORA-1555 error.



CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN

1. Session 1 starts query at time T1 and QENV 50

2. Session 1 selects block B1 during this query

3. Session 1 updates the block at SCN 51

4. Session 1 commits the changes (Now other transactions are free to overwrite this rollback information)

5. A session (Session 1, another session or a number of other sessions) then use the same rollback segment for a series of committed transactions.

These transactions each consume a slot in the rollback segment transaction table such that it eventually wraps around (the slots are written to in a circular fashion) and overwrites all the slots. Note that Oracle is free to
reuse these slots since all transactions are committed.

6. Session 1's query then visits a block that has been changed since the initial QENV was established. Oracle therefore needs to derive an image of the block as at that point in time.

Next Oracle attempts to lookup the rollback segment header's transaction slot pointed to by the top of the data block. It then realises that this has been overwritten and attempts to rollback the changes made to the rollback
segment header to get the original transaction slot entry.

If it 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.


Regds

[padmam]

Hi, 10th May 2001 11:32 hrs chennai

Let us take a simple example like this .

1)create a RBS of a very small size.

2)set transactions what you are going to do now to this transaction explicitly.

3)Open 2 sessions in oracle.

4)in one session create a table and insert around say 50000 record (for single column table) by a simple for loop.Commit it.

5)start updating all this records by a simple update of the single column table.Now issue a update statement.

6)In the alternate session Now try selecting all the records from the table as select * from table name.

7)Now commit in the previous session the updated record while the select in the old section has crossed some say 10,000 records.

8)Now the session selecting the values from the before image of the RBS cannot give you a read consistent image since the value in RBS has been wrapped or over written.

in more simple way to say
==================
Rollback segments hold pre-images of changed data. These pre-images are held for two purposes: to enable a transaction to be rolled back and, crucially, to enable Oracle to return read-consistent views of the data. This means that if you execute a long-running query or transaction, and someone else (or perhaps even your own transaction) subsequently changes the data that your query is accessing, Oracle will use the pre-images in the rollback segment to return the data to your query in the state it was at the moment the query started.

So, where does "snapshot too old" come from? Oracle will not overwrite pre-images in the rollback segments until the transaction which changed the data is complete (ie. committed or rolled back). However, once a commit or rollback has been executed, the pre-images can be overwritten even if they are needed to provide a read-consistent view to another query. "Snapshot too old" simply means that pre-images which the query needs to maintain a read-consistent view have been overwritten.
=====================

To Avoid this Errors
=============
i.Increase the size/number of rollback segments
ii.Do not specify an OPTIMAL size for your rollback segments.
iii.Avoid executing long-running queries when transactions which update the table are also executing.

Cheers

Padmam

[vijay8282]

Hi Padman,

You are saying that aviod using OPTIMAL but i hv never read anything advising not to use OPTIMAL.

I think OPTIMAL is better for managing space. It will keep the rollback segments in control. What do u say ?

[gogo]

optimum is used for spacing n guess will not help in utilising snapshot too old error..coz the DML will run anyhow taking space irrelevant of the optimum parameter n can still give this error...the RBS will allocate new segments until the old ones are released after commit..n released eventually...a bigger RBS will certainly help for read consistent view...

[padmam]

Hi Vijay , 10th May 2001 16:59 hrs chennai

If you dont specify an OPTIMAL size when creating your rollback segments helps because, if set, Oracle will dynamically shrink rollback segments which have grown larger than the specified size by deallocating extents. However, it may deallocate extents which still contain committed pre-images required for read-consistency. Not setting the OPTIMAL value eliminates this cause of "snapshot too old".

Read this article very interesting for more information on snapshot tool old error.

http://home.clara.net/dwotton/dba/snapshot.htm

Cheers

Padmam

[cfcmark]

I managed to prise this out of the Oracle horses mouth...

"The result set of a query is read into memory from where it as accessed by subsequent operations. If the memory copy of the data is flushed before it is used, Oracle will go to the base tables for the data. If that copy of the data is no longer read consistent with the original query, Oracle looks for the consistent copy of the data in the rollback segments. If the rollback copy is no longer available the 1555 message is generated."

That's what Oracle say... Sounds reasonable to me.

One answer is to avoid running long transactions when the same data will be hit by many smaller transactions.

[uday]

test

[nk]

Thank you all.
specially padmam

I did not know that I could "get" so called difficult concepts
"that" easily. Thanks to you guys!!

nk