Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 13 May 2012

01555: Snapshot too old When does it happen? What are the possible causes? How to fix it?

Oracle Rollback Segments (Undo more recently) hold a copy of data before it was modified and they work in a round-robin fashion. Writing and then eventually overwriting the entries as soon as the changes are committed. They are needed to provide read consistency (a consistent set of data at a point in time) or to allow a process to abandon or rollback the changes or for database recovery.
Scenario:

User A opens a query to fetch every row from a billion row table. If User B updates and commits the last row of the billion row table a Rollback entry will be created so User A can see the data as it was before the update. Other users are busily updating rows in the database and this in turn generates rollback – which may eventually cause the entry needed for User A to be overwritten (after all User B did commit the change – so it’s OK to overwrite the rollback segment). Maybe 15 minutes later the query is still running and User A finally fetches the last row of the billion row table – but the rollback entry is gone results ORA-01555: Snapshot too old rollback segment too small
Possible Cause:
From the doc we see that ora-01555 relates to insufficient UNDO storage  or a too small value for  the undo retention parameter.
Rollback records needed by a reader for consistent read are overwritten by other writers.
An active database with an insufficient number of small-sized rollback segments.
A rollback segment corruption that prevents a consistent read requested by the query
A fetch across commits while your cursor is open means happen when a “commit” is use in a loop or inside a procedure frequently or inside a application frequently.
Action:
Do frequent commits.
If in Automatic Undo Management mode increase undo_retention setting otherwise, use larger rollback segments.
Avoiding the ora-01555:

§               Do not run discrete transactions while sensitive queries or transactions are running, unless you are confident that the data sets required are mutually exclusive.
§               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.
§               Code long running processes as a series of restartable steps.
§               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.
§               Use a large optimal value on all rollback segments, to delay extent reuse.
§               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.
§               Use a large database block size to maximize the number of slots in the rollback segment transaction tables, and thus delay slot reuse.
§               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.
§       If necessary, add extra rollback segments (undo logs) to make more transaction slots available.


For better understanding of this issue please read the below article: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923

1 comments:

  1. Trying to find the Best Dating Site? Create an account to find your perfect match.

    ReplyDelete