ORA-01555: snapshot too old


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 undo_retention setting.
Otherwise, use larger rollback segments

Oracle Documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28278/e1500.htm 

Peek into the ORA-01555 reasons:
The ORA-01555 error can occur when a long read only transaction is run against database and there are many DML transactions being executed on database (on same data). The longer query runs, there are more chances of encountering ORA-01555 exception.

The ORA-01555 is caused by Oracle "Read Consistency Mechanism". Oracle provides read consistency by reading the "before image" of updated data from "Online UNDO Segments". If there are lots of updates, long running read-only SQL and a small UNDO, the ORA-01555 error may encounter.

ORA-01555 can be avoided by following precautions:
1. Database should be in Automatic Undo Management mode.
2. Use a large optimal value for undo_retention.
3. Use a large optimal size of rollback segment (undo) size.
4. Should avoid to fetch (select / cursors) between commits.
5. Should Commit less often at the time of long running query, to reduce Undo Segment slot reuse.
6. Try to run long running queries on off peak hours, when there is less DML transactions on database.


Related Posts:
- ORA-00600 internal error code
- ORA-00257 archiver error. Connect internal only, until freed

5 comments:

  1. very helpful detail, thanks for this post !!!

    ReplyDelete
  2. Very useful, Thanks for the post..

    ReplyDelete
  3. What should be the optimal value for undo_retention and how to determine

    ReplyDelete