ORA-01555 Snapshot Too Old

Recently one of our DBAs sent me an e-mail stating that one of our long time running Oracle procedures failed. The error output by the Oracle engine was ORA-01555: snapshot too old: rollback segment number 8 with name “_SYSSMU8$”

I did some research and found the following:

ORA-01555: snapshot too old: rollback segment number string with name “string” too small
Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.
Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.

Well what does all of that mean? Fortunately I found that others have experienced this problem and some tips that will help in solving the problem. Now there are many links with the solution and I’m hoping that by adding one more, it will facilitate finding the answer to those who need it.

From this site Burleson Consulting I found a reference to the original Oracle documentation I had found and additional tips:

  1. – Do not run discrete transactions while sensitive queries or transactions are running, unless you are confident that the data sets required are mutually exclusive.
  2. – Schedule long running queries and transactions out of hours, so that the consistent gets will not need to rollback changes made since the snapshot SCN. This also reduces the work done by the server, and thus improves performance.
  3. – Code long running processes as a series of restartable steps.
  4. – Shrink all rollback segments back to their optimal size manually before running a sensitive query or transaction to reduce risk of consistent get rollback failure due to extent deallocation.
  5. – Use a large optimal value on all rollback segments, to delay extent reuse.
  6. – Don’t fetch across commits. That is, don’t fetch on a cursor that was opened prior to the last commit, particularly if the data queried by the cursor is being changed in the current session.
  7. – Use a large database block size to maximize the number of slots in the rollback segment transaction tables, and thus delay slot reuse.
  8. – Commit less often in tasks that will run at the same time as the sensitive query, particularly in PL/SQL procedures, to reduce transaction slot reuse.
  9. – If necessary, add extra rollback segments (undo logs) to make more transaction slots available.

The above list was summarized from a post by Steve Adams from another IXORA.

I hope these references are useful to someone else who may be experiencing the same issue.

Dog shaking its head!

This can't be happening!

Ok, is not that bad, but when you have something running for a long time, hitting an error like this is annoying to say the least.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: