Applies to:

Oracle Server - Enterprise Edition
Information in this document applies to any platform.

Purpose

This article discusses the means to troubleshoot and diagnose the ORA-01555 "snapshot too old" error
when using the AUM (Automatic Undo Management) feature and outlines the solutions for the different
type of problems.

Scope and Application

For users experiencing the ORA-01555 error:

ORA-1555: snapshot too old (rollback segment too small) 

ORA-1555 Using Automatic Undo Management - How to troubleshoot

1. Collect the relevant information

The following message is reported in the user session

        
ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$" too small

The alert.log will contain a message similar to:

  ORA-01555 caused by SQL statement below (Query Duration=8212 sec, SCN: 0x0000.0088e8d2):
  Tue Aug 22 10:58:43 2006
  SELECT  * FROM SCOTT.BIGEMP e, SCOTT.BIGDEPT d

From this information we know that the ORA-1555 occurred on segment number 9 with name "_SYSSMU9$" and the failing statement. We know that the Query was running for '8212 sec' when the error occured.

In some cases you will see a large number or a 0 for the 'Query Duration', this is most likely because of internal bug 3301573 'ORA-1555 error may report nonsense query start time'

        
Note 3301573.8: Bug 3301573 - ORA-1555 error may report nonsense query start time 

If this is the case, then please note there are other ways of identifying the query duration time, which are discussed lateron in this article.

Special considerations to be made

- Before-images of Lob data are stored in the segment itself, rather then the undo segment/tablespace. Therefor you first need to verify whether LOB columns are used. This need to be done for every table involved. The list of tables can be retrieved from the sql statement in the alert.log.

For example:

SQL> desc bigemp
SQL> desc bigdept

If there are LOB columns involved then proceed with the rest of this article, but if there is no other reason for the ORA-1555 to occur then the solution is to increase the PCTVERSION of the lob column(s).

This is discussed in detail in:

Note 162345.1: LOBS - Storage, Read-consistency and Rollback

- Whether the instance is part of a RAC environment. If this is the case then it is important to know to which instance the undo segment belongs, because it is possible that instance 1 reports an ORA-1555 error but the undo segment is actually allocated to another instance. We need to know on which instance to collect the additional information to troubleshoot the ORA-1555 error.

select stat.inst_id, seg.segment_name, seg.tablespace_name
from dba_rollback_segs seg, gv$rollstat stat
where seg.segment_id = stat.usn
  and seg.segment_name='';

eg.: seg.segment_name='_SYSSMU9$';

Please note that for RAC environments Oracle recommends to use the same undo configuration across the different instances.

2. What additional information do we need ?

- The undo configuration:

SQL> show parameter undo

 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- -------------
 undo_management                      string      AUTO
 undo_retention                       integer     7200
 undo_suppress_errors                 boolean     FALSE
 undo_tablespace                      string      UNDOTBS1

Please note that there are different ways of retrieving the undo parameter values. This is just one of them.

- The undo extent usage
This can be retrieved using the following query. Note that this also gives you the 'Query Duration' time up to the point the ORA-1555 occured:

set pagesize 25
set linesize 100

column UNXPSTEALCNT  heading "# Unexpired|Stolen"
column EXPSTEALCNT   heading "# Expired|Reused"
column SSOLDERRCNT   heading "ORA-1555|Error"
column NOSPACEERRCNT heading "Out-Of-space|Error"
column MAXQUERYLEN   heading "Max Query|Length"

select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN
from gv$undostat
where begin_time between to_date('','MM/DD/YYYY HH24:MI:SS') 
                     and to_date('','MM/DD/YYYY HH24:MI:SS')
order by inst_id, begin_time;

NOTE: With automatic retention tuning (UNDO_MANAGEMENT=AUTO), you can adjust the script. below to include the TUNED_UNDORETENTION column as well column TUNED_UNDORETENTION heading "Tuned Undo|Retention" add TUNED_UNDORETENTION to the select list above.

3. Examples

The following are examples of an ORA-1555 occurance with automatic undo management. Please read the undo algoritm used in:

        
Note 269814.1: ORA-01555 Using Automatic Undo Management - Causes and Solutions

to get a better understanding of the following examples.

  Example #1: undo_retention too low
  ----------

                            # Unexpired  # Expired   ORA-1555 Out-Of-space  Max Query
   INST_ID BEGIN_TIME            Stolen     Reused      Error        Error     Length
   ------- ---------------- ----------- ---------- ---------- ------------ ----------
       ...
         1 08/28/2006 10:20           0          0          0            0       5852
         1 08/28/2006 10:30           0          0          0            0       6252
         1 08/28/2006 10:40           0          0          0            0       6852
         1 08/28/2006 10:50           0         32          0            0       7452
         1 08/28/2006 11:00           0          7          1            0       8212

  The undo_retention was set to 7200 seconds and the Query was running for 8212 seconds 
  before it failed with the ORA-1555. The output shows that we re-used expired extents. 
  To resolve this problem the undo_retention period must be increased to be higher than 
  the 'Query Duration' time.

  Example #2: undo tablespace too small
  ----------

                            # Unexpired  # Expired   ORA-1555 Out-Of-space  Max Query
   INST_ID BEGIN_TIME            Stolen     Reused      Error        Error     Length
   ------- ---------------- ----------- ---------- ---------- ------------ ----------
       ...
         1 08/28/2006 10:20           0          0          0            0          1
         1 08/28/2006 10:30           0          0          0            0          1
         1 08/28/2006 10:40           0          0          0            0          1
         1 08/28/2006 10:50          23          0          0            0        272
         1 08/28/2006 11:00          67          0          1            1        843

  The output shows there was an 'Out-Of-Space' error and that we have stolen several unexpired 
  extents. The undo tablespace was not big enough to respect the undo_retention period, hence 
  extents/blocks were stolen. The solution is to increase the undo tablespace size.
   

  Example #3: RAC environment
  ----------

                            # Unexpired  # Expired   ORA-1555 Out-Of-space  Max Query
   INST_ID BEGIN_TIME            Stolen     Reused      Error        Error     Length
   ------- ---------------- ----------- ---------- ---------- ------------ ----------
         1 08/28/2006 14:58           0          0          0            0       8111  
         1 08/28/2006 15:08           0          0          0            0       8711
         1 08/28/2006 15:18           0          0          1            0       9245
         2 08/28/2006 15:04           0         13          0            0       1020
         2 08/28/2006 15:14          17         16          0            1       1020
         2 08/28/2006 15:24           0          0          0            0       1020

  The ORA-1555 occured in instance 1, however the problem relates to instance 2 where we 
  encountered an Out-Of-Space error and as a result stole some unexpired extents.
  The solution is to increase the undo tablespace in instance 2.

 

References


        
Note 10630.1 - ORA-01555: "Snapshot too old" - Overview
Note 40689.1 - ORA-01555: "Snapshot too old" - Detailed Explanation