Troubleshooting / Documentos / Soporte Técnico / Servicios / Tilsor
 
 

ORA-01555 "SNAPSHOT TOO OLD" - CAUSES AND SOLUTIONS


1. Prob# 1005107.6 ORA-01555 "SNAPSHOT TOO OLD" - CAUSES AND SOLUTIONS
2. Soln# 2028594.6 WORKAROUNDS OR SOLUTIONS FOR ORA-01555


1. Prob# 1005107.6 ORA-01555 "SNAPSHOT TOO OLD" - CAUSES AND SOLUTIONS

Problem ID : 1005107.6
Affected Platforms : Generic: not platform specific
Affected Products : Oracle Server - Enterprise Edition V7
Affected Components : RDBMS V07.XX
Affected Oracle Vsn : Generic

Summary:
ORA-01555 "SNAPSHOT TOO OLD" - CAUSES AND SOLUTIONS

+=+

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


Search Words:
=============

ORA-1555


+==+

Diagnostics and References:

* {975.6,Y,100} ORA-01555: SNAPSHOT TOO OLD (ROLLBACK SEGMENT TOO SMALL)


2. Soln# 2028594.6 WORKAROUNDS OR SOLUTIONS FOR ORA-01555

Solution ID : 2028594.6
For Problem : 1005107.6
Affected Platforms : Generic: not platform specific
Affected Products : Oracle Server - Enterprise Edition V7
Affected Components : RDBMS V07.XX
Affected Oracle Vsn : Generic

Summary:
WORKAROUNDS OR SOLUTIONS FOR ORA-01555

+=+

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.

Solution References:
====================

Note 10581.1 Managing Rollback Segments
Note 10630.1 ORA-01555 "Snapshot too old" - Overview
Note 40689.1 ORA-01555 "Snapshot too old" - Detailed Explanation

@ Note 10579.1 How Many Rollback Segments to have (V6)
@ Note 10595.1 Rollback Segment Extents (V6)