ORA-01555: Snapshot Too Old

UNDO is  the  backbone  of  the  READ CONSISTENCY  mechanism  provided  by  Oracle. Multi-User Data  Concurrency  and  Read  Consistency  mechanism  make  Oracle  stand  tall  in  Relational  Database Management  Systems  (RDBMS) world .

Best  of  all,  automatic  undo  management  allows  the  DBA  to  specify  how  long  undo  information should  be  retained  after  commit, preventing  “snapshot too old”   errors  on  long  running  queries. This  is  done  by  setting  the UNDO_RETENTION  parameter.  The   default  is  900  seconds (5 minutes), and  we  can set  this  parameter  to  guarantee  that  Oracle  keeps undo  logs  for  extended  periods  of time. The  flashback  query  can  go  upto  the  point  of  time specified as a value in  the UNDO_RETENTION parameter.

Why ORA-01555 error occur? 

1.) Oracles  does  this  by reading  the  "before image"  of  changed  rows  from  the  online  undo  segments.  If  we  have  lots  of  updates, long running SQL  i.e , rollback records  needed  by  a  reader  fo  consistent read are overwritten by other writers.

2.) It may also due small size of undo and small undo_retention period .

To  solve  this  issues  we need  to  increase the  undo  tablepsace  and  undo  retention  period. Now  the issue  is  how  much  should  be  the  optimal value of  undo  retention and undo tablespace. For this we use the advisor. By using OEM, it is quite easy to estimate the size and time duration of undo.

Calculate Optimal Undo_Retention  :
The following query will help us to optimize the UNDO_RETENTION parameter :


Optimal Undo Retention = Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_SEC)

To calculate Actual Undo Size :

SQL> SELECT SUM(a.bytes)/1024/1024 "UNDO_SIZE_MB"
            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#;

Undo Blocks per Second : 

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"

FROM v$undostat ;

DB Block Size  :

SQL> SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [Byte]"   FROM   v$parameter
  WHERE name = 'db_block_size';

We can do all in one query as

SQL> SELECT d.undo_size/(1024*1024) “ACT_UNDO_SIZE [MB]“, 
              SUBSTR(e.value,1,25) “ UNDO_RTN [Sec] “, 
              ROUND((d.undo_size / (to_number(f.value) * 
              g.undo_block_per_sec))) “OPT_UNDO_RET[Sec]” 
       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'
/
ACT_UNDO_SIZE [MB]         UNDO_RTN [Sec]              OPT_UNDO_RET[Sec]

------------------------             ----------------------            ----------------------------
                      50                                  900                                              24000

Calculate Needed UNDO Size :

If  we  are  not  limited  by  disk  space, then  it would  be  better  to  choose  the  UNDO_RETENTION time that is best for us (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity :                                                                                                                                                              

Formula :Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_PER_SEC



Here again we can find in a single :

SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       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 [MByte]"
  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'
/

We can avoid ORA-01555  error as follows :

1.) Do not run discrete transactions while sensitive queries or transactions are running, unless we 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.) 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.


Enjoy       :-)