What snapshot, and why is it too old?

May 4, 2011 at 10:44 am | Posted in Oracle, Technical Tips | 17 Comments

I wanted to address a fairly technical topic in Oracle for this go-round. One of the most mysterious and confusing error messages you sometimes get when working in an Oracle database is “snapshot too old”. What the heck is that all about? Well, here’s the set of circumstances that can happen which can cause this error.

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.

Now it’s your turn. Respond to my blog and let me know what kind of steps you might take as the DBA to reduce the likelihood that anyone working in your database will get the error message “snapshot too old.” Give us your thoughts by May 31st. Thanks!

–Bob, aka OrclTestGuy

ETA: Be sure to read Bob’s followup post here: https://transcender.wordpress.com/2011/07/13/keeping-old-snapshots-young-with-some-oracle-magic/

~the Editrix


RSS feed for comments on this post. TrackBack URI

  1. Hello Bob,

    Thank you allot for a such a wonderful explanation. I tried allot to find the correct explanation, finally I reached to your blog. It’s awesome.

    Sumeet Kumar,
    Mail to: sumeet.august@gmail.com

  2. Will you please provide the email id of yours..?

    mail to: sumeet.august@gmail.com

  3. Thanks a really a good explanation..Thanks a lot…

  4. Really a very good explanation….keep posting …

  5. Very clear and easily undersood. Awesome job.

  6. too easy to understands………. Thanks keep posting

  7. Simply amazing…. Crystal clear. Thanks Bob.

  8. a break down a 5th grader would understand!

  9. really great explanation…good for new comers and experienced ones also…!!!

  10. very good explanation!

  11. Sooper explaination.

  12. great explanation.. thanks

  13. Awesome explanation… excellent. Thanks.

  14. awesome

  15. Really nice explanation..

  16. Very clear explanation. Thanks!

  17. Very nice
    Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Entries and comments feeds.

%d bloggers like this: