ORA-01555: snapshot too old - Causes and Solutions [ID 1005107.6]

Problem Description:
====================

A transaction fails with an ORA-01555 error.

ORA-01555: snapshot too old (rollback segment too small)
    Cause: rollback records needed by a reader for consistent read are
           overwritten by other writers


Problem Explanation:
====================

The ORA-01555 is a typical error whereby a user transaction fails.  There are
a few common cases where the ORA-01555 may appear:

1. Few number of rollback segments with potentially small sizes in a
   very active database (e.g., multiple users doing on-line transaction
   processing -- OLTP).

2. Corrupted rollback segment prevents a consistent read requested by a
   query from being able to read the block in the rollback segment.

3. Fetch across commits within an open cursor (cursors retain the "snapshot"
   of the query at cursor open time).

4. Fetch across commits with delayed block cleanout (the data block is not
   updated with the last committed image until the next reader accesses the
   data block, which requires verification from the transaction table of
   the rollback segment). 
   
Solution Description:
=====================

The cause of the error invariably involves a mix of the following: 
  * the set-up of your rollback segments
  * the number and nature of the concurrent transactions

Keep in mind that the workarounds/solutions for all cases of ORA-01555 do
not eradicate the error.  However, they can potentially reduce the 
probability of its reoccurrence.

------------
For case #1:
------------
This is probably the most typical one and the easiest one to fix. 
In an OLTP environment, you should consider having a good number of rollback
segments with decent size per extent (depending on the demands of your
applications and the number of users concurrently logged on to the server at
a given time of the day).

For batch jobs (for instance, report generators that execute multiple
complex queries), it is best to assign the transaction to a larger rollback
segment (both in terms of the size per extent and the number of extents
that the segment can have before maxing out).

------------
For case #2:
------------
It is best to drop and recreate the rollback segment while the database 
is up and running (provided that no active transactions are currently
accessing it).  If the corruption of the rollback segment prevents the
database from being opened, then it is a side issue from ORA-01555 and you
should call Oracle Support Services for instructions on how to deal with
such a scenario.

------------
For case #3:
------------
Committing less often (which will result in larger rollback segments) will
REDUCE the probability of getting the ORA-01555.  Thus, there is a
trade-off between disk space and success in cursor processing.

Example:
-------
Consider the following scenario:
  Let table EMP contain an index (e.g., a primary key) 
  applied to the EMPNO column.
 
DECLARE 
  cursor c1 is select empno, sal from emp where empno > 0; 
               -- The WHERE clause should force the index to be traversed
               -- and the rows returned ordered by empno 
  empno_val     number;  
  sal_val       number; 
BEGIN 
open c1; 
LOOP 
   fetch c1 into empno_val, sal_val; 
   if c1%FOUND then 
        -- Give a salary raise of 4%
        update emp set sal = sal_val * 1.04 where empno = empno_val; 
        commit; 
   else exit; 
   end if; 
END LOOP; 
close c1; 
END;
/
 

Let us consider the case in which there is only one VERY SMALL, ONLINE
rollback segment available to store the undo generated by the UPDATE above.
Let us also assume that the table rows are inserted such that they are
physically spread (with respect to the empno values) across all datablocks in
a nonsequential manner.  Furthermore, within a particular block, the values of
our key value (i.e., empno) are also very distant from each other.  For
example, if empno = {1..1000}, we have:
                              
  TABLE EMP:

      Block  1         Block  2     . . .    Block  N 
    -------------    -------------         ------------- 
   |   empno     |  |   empno     |       |  empno      | 
   |.............|  |.............|       |.............|
   |      1      |  |      2      |       |   301       | 
   |   1000      |  |     13      | . . . |   366       | 
   |    220      |  |      4      |       |   999       |  
   |    456      |  |    300      |       |   865       | 
    -------------    -------------         ------------- 
 
The idea is that, by the time we are trying to update employee number 1000,
there will already be 999 committed updates.  If the VERY SMALL rollback
segment has wrapped around since the salary of employee number 1 has been
updated (very likely for a VERY SMALL rollback segment), then the ORA-01555
will appear by the time the RDBMS tries to fetch the read-consistent version
of Block #1 when updating employee record 1000.
    
Recall that the Oracle Server provides read consistency at the BLOCK level.
Therefore, any uncommitted changes to a BLOCK must have undo entries applied 
for any subsequent attempts to read that block.  If the undo entry has been
overwritten in the rollback segment, the update will fail with ORA-01555.
 
------------
For case #4:
------------
It is a good idea to perform. a full table scan on all involved tables before
processing the cursor, so that the delayed block cleanout is done before
handling the cursor.


**CAVEAT FOR CAUSES #3 AND #4:
  ---------------------------
  Doing fetch across commits is not an ANSI supported operation. 
  According to ANSI standard, a cursor is invalidated when a commit 
  is performed and should therefore be closed and reopened. 
  Oracle, however, allows application programmers and users to do fetch
  across commits.  Hence, they should be aware of the potential ORA-01555
  error and the fact that they are relying on a cursor behavior. that is
  not ANSI standard.       


Additional References:
======================
Note:62005.1   Creating, Optimizing, and Understanding Rollback Segments
Note:10630.1   ORA-01555 "Snapshot too old" - Overview
Note:40689.1   ORA-01555 "Snapshot too old" - Detailed Explanation

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26104736/viewspace-707094/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26104736/viewspace-707094/