Applies to:

Purpose

Scope and Application

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 occurred.

In some cases you will see a large number or a 0 for the 'Query Duration'.  Seeing 0 for the query duration 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 later on 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. Therefore you first need to verify whether LOB columns are used.  This need to be done for every table involved in the problem code.  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 no LOB columns as part of the problem, then proceed with the rest of this article, but if there is no other reason for the ORA-1555 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 

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 occurred:

REM In this example, the errors happened at 
REM 11:25am on 30-APR-2011

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('04/30/2011 08:25','MM/DD/YYYY HH24:MI') 
                     and to_date('04/30/2011 11:25','MM/DD/YYYY HH24:MI')
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 with automatic undo management. Please read the undo algorithm 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.   

Note 262066.1: How To Size UNDO Tablespace For Automatic Undo Management

  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 occurred 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.

Remarks

 

References


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

返回页首