Skip navigation

You've probably encountered the error ORA-01555: snapshot too old during the administration of your Oracle Database.

 

                                                                         

Time 1Session #1 starts a query on table A
Time 2Session #2 updates row X in table A
Time 3The query from session #1 arrives at row X and  discovers the last updated time (based on  the SCN number) of the row to be later than Time 1. So the UNDO or LOB  segments (depending on the segment type) are used to get the read consistent view (before image) of the  row  and then the query proceeds
Time 4Session #2 updates row Y in table A and then  commits (thus making it possible  for this transaction slot to be overwritten)
Time 5Session #2 updates row Z in table B and  commits the record. Due to sub optimal configuration, mostly lack of space, the read consistent view for the  update  of row Y in table A at time 4 is overwritten (we won't examine why the  overwrite  occurs at this point)
Time 6The query from session #1 arrives at row Y and discovers the last updated time (based  on the SCN number) of the row to be later than Time 1. So the UNDO or  LOB segments are examined to find the read consistent view. BUT the  transaction slot containing the view was overwritten at time 5 so no  read  consistent view is available and an ORA-1555 occurs.

 

NOTE: UNDO is a generic term that can refer to either System managed UNDO (or Automatic Undo Management AUM) or Rollback segments

 

 

In which files is the error recorded?

 

The error is usually listed in one of the following files.

 

  • Alert Log file
    Messages such as the example below are generated:
    ORA-01555: snapshot too old: rollback segment number 107 with name "_SYSSMU107_1253191395$" too small

 

  • Trace File generated at the time of the issue
    By default, no trace file is being generated automatically for ORA-1555, unless the 1555 event is set during the error occurrence
    alter system set events '1555 trace name errorstack level 3';
    In this case, the trace file name is reported in the alert log file whenever an 1555 error occurs - for example:
    Wed Jul 22 09:20:11 2015
    Errors in file /u01/sq/diag/rdbms/DB1/ DB1/trace/ DB1_ora_35236.trc:
    ORA-01555: snapshot too old: rollback segment number 34 with name "_SYSSMU34_417799848$" too small

 

 

Why does the error occur?

 

The root cause of this issue is mainly due to an UNDO block that has been overwritten and the query cannot build a copy of the data

at the time the query started. The common known issues are listed below:

 

  • The UNDO record for the row has expired.

    This means that the current time minus the commit time of the row is greater than the UNDO_RETENTION. Once an UNDO record
    of a committed row is 'expired', it is available for reuse.
    You may ask the question
    Why do my queries sometimes run so much longer than my UNDO_RETENTION before encountering an ORA-1555 and other times it occurs soon after?
    There is no definite answer and it all depends on the amount of activity and how busy the UNDO tablespace is.

    NOTE: The UNDO records for an active or uncommitted transactions are marked as 'ACTIVE'. Once the transaction commits, the respective
    UNDO records are marked as 'UNEXPIRED' ie, it's been retained for the duration mentioned by UNDO_RETENTION parameter (or TUNED_UNDORETENTION
    calculated by the system, incase of AUM). After retaining the UNDO records for the retention time specified, they are marked as 'EXPIRED' and are available for reuse.

 

  • The UNDO record for the row has NOT expired and yet was still overwritten.
    This scenario occurs when the UNDO tablespace has become full and the 'UNEXPIRED' UNDO records are being overwritten. This happens if the
    RETENTION GUARANTEE is not enabled for the UNDO tablespace.

 

  • The LOB segment read consistent copy of the LOB segment is no longer available
    This depends on how the LOB column is configured, in-row or out-of-row. In-row LOBs should be utilizing normal UNDO algorithms in the UNDO tablespace.
    For Out-of-row LOBs use the information below.

    Read consistency with LOBs is controlled by 2 methods
      • Use of PCTVERSION (old method)
        Specify the maximum percentage of overall LOB storage space used for maintaining the old version of the LOB. The default value is 10,
        meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.
        The downfall to this method is that through frequent updates or deletes in tables with LOBs it often exceeds even 100% PCTVERSION
      • Use of RETENTION (current method for use with AUM)
        Use this clause to indicate that Oracle Database should retain old versions of this LOB column.
        Oracle Database uses the value of the UNDO_RETENTION initialization parameter to determine the amount of committed UNDO data to retain in the database.
        This method uses the same expiration as UNDO segments. If an ORA-1555 error occurs with a LOB segment, then this means that either:
        • PCTVERSION was exceeded and the read consistent view of the LOB was overwritten
          or
        • the LOB has exceeded RETENTION and the row LOB was overwritten sometime during the query that generated the ORA-1555

 

Troubleshooting

 

Below is a list of steps to troubleshoot the issue:


1. Check Error Messages
Check alert log (or the logfile that contains the error) to determine the details of the error message as there are different types of ORA-1555 error messages:

     a. Identify  segment name
         ORA-01555: snapshot too old: rollback segment number with name "" too small
         --> notice that segment name is null ""
         or/and
        ORA-22924: snapshot too old
         In this case 1555 error is reported while accessing UNDO data stored inside LOB segment. ORA-01555 for LOB segment is caused by one of the following reasons :

             1. LOB segment corruption:
                 To check the LOB table for corruption, review the following document :

                       Document 452341.1 ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob  Corruption

             2. If no LOB corruption is found, check for issues with Retention/Pctversion values :
                 You may need to increase Retention/Pctversion. Review the following document

                        Document 846079.1 LOBs and ORA-01555 troubleshooting

                ORA-01555: snapshot too old: rollback segment number 107 with name "_SYSSMU107_1253191395$" too small

               -> notice that in this case the segment name exists "_SYSSMU107_1253191395$" which means UNDO data inside UNDO tablespace.

                 In this case, ORA-1555 error is reported while accessing UNDO data at UNDO tablespace which will discuss how to troubleshoot in following steps

       b. Identify Query Duration
           Failed query duration is logged in some ORA-1555 error messages in the alert log file or an application log.
           ORA-01555 caused by SQL statement below (Query Duration=1974 sec, SCN: 0x0002.bc30bcf7):
           If the query duration = 0 or few seconds, check the following document

                 Document 1131474.1 ORA-01555 When Max Query Length Is  Less Than Undo Retention, small or 0 Seconds

           If the query duration is higher than the UNDO_RETENTION parameter set, increase the UNDO_RETENTION value to meet the query duration.

           Ensure to increase the UNDO tablespace size accordingly. If the query duration is equal to or closer to UNDO_RETENTION value, proceed with the following analysis.

 

 

2. Check Undo Datafiles
    select tablespace_name, file_id, sum(bytes)/1024/1024 a, sum(maxbytes)/1024/1024 b, autoextensible
    from dba_data_files
    where tablespace_name in
    (select tablespace_name from dba_tablespaces where retention like '%GUARANTEE' )
    group by file_id, tablespace_name, autoextensible
    order by tablespace_name;

    If you are using non-autoextensible UNDO data files, this can lead to high calculation of TUNED_UNDORETENTION and hence high allocation

    of UNDO space especially with large UNDO data files.
    To avoid this make sure that the UNDO data files are autoextensible (with MAXSIZE specified) even if you have enough free space.

    NOTE: It is strongly recommended NOT to have both autoextensible and non-autoextensible UNDO data files in the UNDO tablespace as this will

                 be lead to TUNED_UNDORETENTION mis-calculation


3. Check TUNED_UNDORETENTION
     SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;
     SQL> select max(maxquerylen),max(tuned_undoretention) from dba_hist_undostat;
          
a. TUNED_UNDORETENTION is less than the MAXQUERYLEN value:

               This indicates there is a space pressure in the UNDO tablespace and hence the UNDO records are not retained for enough time period.

               Adding space to the UNDO tablespace helps to resolve these cases.

            b. TUNED_UNDORETENTION is too high compared to the MAXQUERYLEN value:
                This generally happens in case of UNDO tablespace with non-autoextensible datafiles. The internal algorithm tries to retain the UNDO

                 records as long as possible in such cases and hence high value of TUNED_RETENTION. The workaround will be to set all UNDO

                 datafiles to autextensible mode. (You can set a MAXSIZE for the datafiles)
                Long running queries can raise TUNED_UNDORETENTION to very high values.
                You need to tune these queries to avoid retain UNDO data for more time inside UNDO tablespace. To identify these long queries, run the following :
                select maxquerysqlid, maxquerylen from dba_hist_undostat order by maxquerylen desc;
                select maxqueryid, maxquerylen from v$undostat order by maxquerylen desc;
                         

4. High utilization of ACTIVE/UNEXPIRED extents
    select distinct status,tablespace_name, sum(bytes), count(*) from dba_undo_extents group by status, tablespace_name;
    Excessive allocation of ACTIVE/UNEXPIRED extents can be caused by one of the following reasons:For more details refer to                   

             a. Large value of UNDO_RETENTION or TUNED_UNDORETENTION as described above.

             b. Large UNDO data generation at certain point of time which can be identified by run following query :

                 alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

                 select begin_time , undoblks from dba_hist_undostat order by undoblks desc ;

                 select begin_time , undoblks from v$undostat order by undoblks desc;

             c. Large dead transaction(s) rollback

             d. Using flashback data archive

     For more details refer to

                 Document 1337335.1 How To Check the Usage of Active Undo  Segments in AUM

5. UNDO_RETENTION :

    We recommend to set UNDO_RETENTION to at least the average of MAXQUERYLEN and increase it if the ORA-1555 error is still reported.
    select avg(maxquerylen) from dba_hist_undostat;
    select avg(maxquerylen) from v$undostat;

                        1.                      

 

Additional Info:

 

For further information, please refer to

or ask questions in the Database Administration community.

Welcome to the My Oracle Support Community! We highly encourage you to personalize your community display name to make your activity more memorable. Please see https://community.oracle.com/docs/DOC-1022508 for instructions.