What is Snap Shot Too Old Error (ORA-01555)

Got from one of the blogs:

You’re probably aware that Oracle stores the changes made by a user in what’s referred to as an undo segment (called a rollback segment prior to Oracle 10g). While a user transaction is progressing, Oracle is keeping track of the “old” version of the changing data for that transaction. Why, you may ask! Well, at the end of the transaction, should the user decide to rollback rather than commit, Oracle will have a quick place to go to get the old version of the data to “undo” all of those changes it made.

If a different user queries data that the original user is in the processing of changing, the second user is going to see the “old” version of the data, assuming the commit has not yet happened. Where do you think Oracle can go to find the old version of the changing data? You got it: in the undo segment of the user who is performing the transaction.

When a user’s transaction is complete (either a COMMIT or ROLLBACK has occurred), Oracle no longer needs the old version of the data. That means the undo segment is released so that the space on the undo segment can be used if and when it’s needed by another transaction. The data on the undo segment isn’t erased, but rather Oracle knows that space is available when needed.  If Oracle does need that space for keeping track of the undo of another user’s transaction, the data from the old transaction will be overwritten at that time.  If Oracle doesn’t need that space, the data from the old transaction will still be sitting out there on disk.

Now, Oracle guarantees you a read-consistent view of the data when you issue a SQL command. For example, if a user issues a SELECT statement at 9:00 AM to retrieve 50,000 rows from a table, Oracle guarantees that the 50,000 rows which print out will all represent the data as it existed at 9:00 AM. With that many rows, of course, it could take 2 hours to print this report. Can someone change the data on one of those rows while the other user is SELECTing? Well, sure; the first person to get to the table was doing a SELECT, and that doesn’t require any locks on the rows of that table.

So what happens if, at 9:21 AM in this scenario, a different user performs an update to the 50,000th row in the table of 50,000 rows being printed by the initial user? The user doing the update will have the “old” version of the data for that row stored in a undo segment. That’s the information that Oracle will need when it’s time to print that row, since that’s the way Oracle can reconstruct that row at print time. As the print job approaches the end of that 2-hour SELECT, Oracle will needs that undo data in order to print the final row in a read-consistent fashion. If it looks in the table for the 50,000th row, it’s going to see the data as it appeared at 9:21 AM, not at 9:00 AM.

Now, is the undo segment from the 9:21 AM transaction still there? Well, the answer is “maybe.” At 9:21 AM, the user performing the update issued the COMMIT command, and as a consequence of that command, the undo segment holding the old version of the data was returned for reuse by a new transaction as needed. The 9:00 AM data (which was the undo data for the 9:21 AM transaction) might still be available at 10:59 AM – or it may have been overwritten by more recent transaction. It just depends on whether Oracle needed that space for a new undo segment for someone else.

So if the data happens to still be there in the undo segment, that last of the 50,00 rows can be reconstructed back to the way it looked at 9:00 AM. The row prints and no one is the wiser. If the undo data for 9:21 AM transaction is no longer available, Oracle will not be able to reconstruct that last row, the report stops printing, and the user running the report gets the error message “snapshot too old.” This occurs because if Oracle printed the updated version of that last row, the user performing the SELECT wouldn’t get a read-consistent report of the data in that table as it looked at the time the SELECT command was issued (9:00 AM).

I like to think of the error message “snapshot too old” as meaning that Oracle is not able to live up to its promise. The promise to the user with the long running SELECT is that he or she will get a consistent view of that data at the time the SELECT command was issued. The “snapshot too old” error message occurs when Oracle is unable to live up to that promise due to these set of circumstances.

--------------------------------------=======================================---------------------------------

Undo data is stored in the undo tablespace, the tablespace nominated by the UNDO_TABLESPACE instance parameter. This tablespace should be sized according to the rate at which undo data is being generated and the length of the queries running in the database.

But even if the undo tablespace is inadequately sized and therefore comes under space pressure, automatic undo retention tuning will tend to minimize problems.

Undo data generated by a transaction must always be kept until the transaction commits. This is an absolute; under no circumstances will Oracle ever overwrite undo data that might be needed to rollback a transaction. This data is known as “active” undo data.

Once a transaction has committed, its undo data is no longer active, but it could still be needed to support long running queires that began before the transaction. Data that may be needed for this purpose is known as “unexpired” undo data.

 ”Expired” undo is data that is no longer needed either to rollback a transaction or to be read by queries.. Active undo will never be overwritten, and ideally expired undo can be safely overwritten at any time.

Unexpired undo can be overwritten, but at the risk of causing queries to fail with the “ORA-01555 : Snapshot too old”error.

The point at which data transitions from “unexpired” to “expired” is controlled by the instance parameter UNDO_RETENTION.

With release 9i of the Oracle database,Oracle would overwrite any expired undo. This meant that if the UNDO_RETENTION parameter were not set appropriately or (not set at all, in which case it defaults to 9oo seconds), there would be great danger of ORA-1555 errors.

Oracle 10g relaeas of the database effectively ignores the UNDO_RETENTION parameter. It will always overwrite the oldest bit of undo data.This means that is a sense there is no longer any difference between expired and unexpired undo and that the UNDO_RETENTION instance parameter is redundant, because undo retention is automatically tuned for the longest possible query.

To monitor the automatic undo retention tuning ,query V$UNDOSTAT this query will show,

SQL> SELECT BEGIN_TIME,END_TIME,TUNED_UNDORETENTION,ACTIVEBLKS,UNEXPIREDBLKS,EXP
IREDBLKS FROM V$UNDOSTAT;

Iin fivteen minutes intervals, how old(in seconds) the oldest block of inactive undo data was. provided that no query started earlier than that, you will never receive a snapshot too old error. The larger the undo tablespace is and the less the transaction workload is, the further back the TUNED_UNDORETENTION will be.