Snapshot Too Old: ORA-01555 Error.

ORA-01555 Error:

1. Problem: 

Below are the settings for the undo tablespace:

undo_retention - 1200

undo_management  - AUTO 
The user encounters the following error in the job that is running in the database:
Ora-01555 snapshot too old error.

2. Impact: Medium to high because it would effect the long running queries due to insufficient undo tablespace thus impacting performance. Also could be a part of a batch process.

3. Solutions: The Ora-01555 snapshot too old error occurs when the undo tablepspace storage space is smaller as compared to the space needed by long running queries. It could also occur because of inappropriate (too small) value of the undo_retention. Undo_retention specifies the time period (in seconds) till which the system retains undo ie. undo would be retined for at least the time specified in this parameter. The undo_retention parameter would be efficent if the current undo tablespace has enough space. If there is an active transaction which  requires undo space and there is not enough available space, then the system reuses unexpired undo space. This causes some queries to fail with a snapshot too old error message. The underlying technology that undo supports is the Oracle read consistency mechanism. 

Below are the remedies to address and remedy this error:

1. Reduce and delay extent reuse by increasing the size of the undo tablespace and the undo_retention parameter .

2. Try not to do a fetch between the commits. So, if a cursor was opened before the last commit  don't fetch the cursor as it is still performing actions in the current sessions.

3. Don't perform frequent commits as this would reduce the size of the undo tablespace and also the queries would take more time.

4. Try to perform the long-running queries when the system has the least load of DMLtransactions.

5. Set a large value for the  database block size(db_block_size) parameter to reduce and delay extent reuse. 

6. Run separate transactions while the sensitive long-running queries are taking place only when it is very important and the transactions are not dependent on each other and do not prejudice each others performance.

7.  Before you run long-running and sensitive sql queries make sure that you have sufficient and optimal undo tablespace. If you do not have sufficient undo tablespace manually resize it to prevent rollback failure thus preventing the error.

8. You can also calculate the size of the optimal undo_retention, undo tablespace and the db_block_size before hand.

9. You can manually manage the usage, size and the amount of the rollback segments.