ORA-01555 caused by SQL statement - Snapshot Too Old

Q: Why do we get ORA-01555 (Snapshot Too Old) error?.

Ans: Suppose there are two users(User A and User B). User A is executing long update statement on oracle data without commit and at the same time User B is also selecting same data which is getting updated by User A.

Both Queries are still running and no one gets output yet.

After some time, Query execution of User A gets completed and he commits the data. Now what does happen with User B?. 

He gets ORA-01555 error. The reason behind this is ,"User B doesn't get old image of the data". 

Because User A has changed the data.

Solution:

• Increasing UNDO tablespace size is not the solution for this.
• Run Undo advisory
• Increase UNDO Retention Period
• Enable Undo Retention Guarantee
• Run this Select Query during non-business hours where heavy transactions are not happening.
• Tune the Query

I have faced below three types of ORA-01555 errors in my career.

1. ORA-01555 with Query Duration=23456 sec (This is just an example 23456).
2. ORA-01555 with Query Duration=0 sec
3. ORA-01555 with Unrealistic Query Duration=23456789900 (This is just an example of unrealistic 23456789900) 

ORA-01555 with Query Duration=23456 sec
Check the alert log file, you will get trace file for this error. You will get affected sessions with details. Below are the different solutions for this error.

1. Run these queries during non business hours which will reduce the work and would help to improve the performance.

2. Run UNDO Advisory and add more undo space if recommended.

SQL> col "ACTUAL UNDO SIZE [MByte]" for 999999999
SQL> col "UNDO RETENTION [Sec]" for a20
SQL> col "OPTIMAL UNDO RETENTION [Sec]" for 999999999
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MB]",SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",(TO_NUMBER(e.value) * TO_NUMBER(f.value) *g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MB]" FROM (SELECT SUM(a.bytes) undo_size FROM v$datafile a,v$tablespace b,dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#) d,v$parameter e, v$parameter f,(SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size';

3. Avoid frequent commits.

4. Increase UNDO retention period.

5. Enable UNDO Retention Guarantee.

6. Tune the Query.

ORA-01555 with Query Duration=0 sec

1. If the 1555 error is consistently reported against a certain table each time, so this most like is caused by Indexes/table mismatch and you need to drop/recreate (not rebuild) all table indexes. 

2. Bug 8231583 reported on this issue is closed due to the lack of re-producibility. 

There are a few workarounds identified for this issue. 

1) Create a new undo tablespace and switch to the new Undo tablespace. 

For example: 

SQL> CREATE UNDO TABLESPACE UNDOTBS_NEW DATAFILE '/u01/oracle/DC/undotbs01.dbf' SIZE 100M REUSE AUTOEXTEND ON; 

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_new; 

The old undo can only be dropped after all transactions have cleared from the undo tablespace. 

2) Disable auto tuning by setting hidden parameter (undo autotune)

3) If this is still consistently reproducible in your database then communicate this with Oracle Support.

ORA-01555 with Unrealistic Query Duration=23456789900

This is the Bug:10415967 - ORA-1555 SHOWS UNREALISTIC QUERY DURATION in Oracle.

Unrealistic query duration is reported when the start time is not initialised within the cursors.

Solution:

You can check with Oracle Support to check solution for fix this bug.







Thanks for reading this post ! Please comment if you like this post !