8/8/2019 ORA-01555

1/9

ORA-01555: "Snapshot too old" - Overview

There are various reasons why customers can get the error ORA-01555. Sometimes it is due to rollbacksegments being too small in size, but there are other reasons. This bulletin is an attempt to give acomplete summary of all the situations which would cause an ORA-01555 error and how to resolve them.In order to understand the bulletin, one needs to understand some of the internal mechanisms of Oracle,

so we start by explaining briefly about read consistency and block cleanouts.

Oracle always enforces statement-level read consistency. This guarantees that the data returned by asingle query is consistent with respect to time when the query began. Therefore, a query never sees thedata-changes made by transactions that commit during the course of execution of the query.

Oracle uniquely identifies any given point in time by a set of numbers called the System Change Numbers(SCN). So SCN can be defined as the state of the database at any one given point in time. To produceread-consistency, Oracle marks the current SCN as the query enters the execution phase. The query canonly see the snapshot of the records as they were at the time of marked SCN.

Oracle uses rollback segments to reconstruct the read-consistent snapshot of the data. Whenever atransaction makes any changes, a snapshot of the record before the changes were made is copied to arollback segment and the data block header is marked appropriately with the address of the rollbacksegment block where the changes are recorded. The data block also maintains the SCN of the lastcommitted change to the block.

As the data blocks are read on behalf of the query, only blocks with lower SCN than the query SCN willbe read. If a block has uncommitted changes of other transactions or changed data with more recentSCN, then the data is reconstructed using the saved snapshot from the rollback segments. In some raresituations, if RDBMS is not able to reconstruct the snapshot for a long running query, the query results inan ORA-01555 error.

A rollback segment maintains the snapshot of the changed data as long as the transaction is still active(commit or rollback has not been issued). Once a transaction is committed, RDBMS marks it with currentSCN and the space used by the snapshot becomes available for reuse.

Therefore, ORA-01555 will result if the query is looking for the snapshot which is so old that rollbacksegment information could not be found because of wrap around or overwrite.

Situations Where ORA-01555 Errors Commonly Occur

1. Fewer and smaller rollback segments for a very actively changing database

If the database has many transactions changing data and commiting very often, then the chance of

reusing the space used by a committed transaction is higher. A long running query then may not be able

to reconstruct the snapshot due to wrap around and overwrite in rollback segments. Larger rollback

segments in this case will reduce the chance of reusing the committed transaction slots.

2. Corrupted rollback segment

If the rollback segment is corrupted and could not be read, then a statement needing to reconstruct a

before image snapshot will result in the error.

3. Fetch across commit

This is the situation when a query opens a cursor, then loops through fetching, changing, and committing

8/8/2019 ORA-01555

2/9

the records on the same table. In this scenerio, very often an ORA-01555 can result. Let's take the

following example to explain this:

A cursor was opened at SCN=10. The execution SCN of the query is then marked as SCN=10. Every

fetch by that cursor now needs to get the read-consistent data from SCN=10. The user program is now

fetching x numbers of records, changing them, and committing them. Let's say they were committed with

SCN=20. If a later fetch happens to retrieve a record which is in one of the previously committed blocks,

then the fetch will see that the SCN there as 20. Since the fetch has to get the snapshot from SCN=10 it

will try to find it in the rollback segments. If it could rollback sufficiently backwards as previously

explained, then it could reconstruct the snapshot from SCN=10. If not, then it will result in an ORA-01555

error.

Committing less often which will result in larger rollback segments will REDUCE the probability of getting

'snapshot too old' error.

4. Fetch across commits with delayed block clean out

To complicate things, now we see how delayed block clean outs play an important role in getting thiserror.

When a data or index block is modified in the database and the transaction committed, Oracle does a fast

commit by marking the transaction as committed in the rollback segment header but does not clean the

datablocks that were modified. The next transaction which does a select on the modified blocks will do

the actual cleanout of the block. This is known as a delayed block cleanout.

Now, take the same scenario as described in previous section. But instead of assuming one table, let us

assume that there are two tables in question. i.e: the cursor is opened and then in a loop, it fetches from

one table and changes records in another, and commits. Even though the records are getting committed

in another table it could still cause ORA-01555 because cleanout has not been done on the table from

which the records are being fetched.

For this case, a full table scan before opening and fetching through the cursor will help.

Summary:

Fetches across commits as explained in the last two cases are not supported by ANSI standard.

According to ANSI standard a cursor is invalidated when a commit is performed and should be closed

and reopened. Oracle allows users to do fetch across commits but users should be aware that it might

result in ORA-01555.

8/8/2019 ORA-01555

3/9

ORA-01555 "Snapshot too old" - Detailed Explanation

Terminology

Two key concepts are briefly covered below which help in the understanding of ORA-01555, ReadConsistency and Delayed Block Cleanout.

1. Read Consistency

This is documented in the Oracle Database Concepts manual and so will not be discussed further.However, for the purposes of this article this should be read and understood if not understood already.Oracle Server has the ability to have multi-version read consistency which is invaluable to you because itguarantees that you are seeing a consistent view of the data (no 'dirty reads').

2. Delayed Block Cleanout

This is best illustrated with an example: Consider a transaction that updates a million row table. Thisobviously visits a large number of database blocks to make the change to the data. When the usercommits the transaction Oracle does NOT go back and revisit these blocks to make the changepermanent. It is left for the next transaction that visits any block affected by the update to 'tidy up' theblock (hence the term 'delayed block cleanout').

Whenever Oracle changes a database block (index, table, cluster) it stores a pointer in the header of thedata block which identifies the rollback segment used to hold the rollback information for the changesmade by the transaction. (This is required if the user later elects to not commit the changes and wishes to'undo' the changes made.)

Upon commit, the database simply marks the relevant rollback segment header entry as committed. Now,when one of the changed blocks is revisited Oracle examines the header of the data block whichindicates that it has been changed at some point. The database needs to confirm whether the change hasbeen committed or whether it is currently uncommitted. To do this, Oracle determines the rollbacksegment used for the previous transaction (from the block's header) and then determines whether therollback header indicates whether it has been committed or not.

If it is found that the block is committed then the header of the data block is updated so that subsequentaccesses to the block do not incur this processing.

This behaviour is illustrated in a very simplified way below. Here we walk through the stages involved inupdating a data block.

Stage 1 - No Changes Made

Description: This is the starting point. At the top of the data block we have an area used to link activetransactions to a rollback segment (the 'tx' part), and the rollback segment header has a table that storesinformation upon all the latest transactions that have used that rollback segment.

In our example, we have two active transaction slots (01 and 02) and the next free slot is slot 03. (Sincewe are free to overwrite committed transactions.)

8/8/2019 ORA-01555

4/9

Stage 2 - Row 2 is Updated

Description:We have now updated row 2 of block 500. Note that the data block header is updated to

point to the rollback segment 5, transaction slot 3 (5.3) and that it is marked uncommitted (Active).

Stage 3 - The User Issues a Commit

Description: Next the user hits commit. Note that all that this does is it updates the rollback segment

header's corresponding transaction slot as committed. It does *nothing* to the data block.

Stage 4 - Another User Selects Data Block 500

Description: Some time later another user (or the same user) revisits data block 500. We can see that

there is an uncommitted change in the data block according to the data block's header.

Oracle then uses the data block header to look up the corresponding rollback segment transaction tableslot, sees that it has been committed, and changes data block 500 to reflect the true state of thedatablock. (i.e. it performs delayed cleanout).

8/8/2019 ORA-01555

5/9

ORA-01555 Explanation

There are two fundamental causes of the error ORA-01555 that are a result of Oracle trying to attain a'read consistent' image. These are:

y The rollback information itself is overwritten so that Oracle is unable to rollback the (committed)transaction entries to attain a sufficiently old enough version of the block.

y The transaction slot in the rollback segment's transaction table (stored in the rollback segment'sheader) is overwritten, and Oracle cannot rollback the transaction header sufficiently to derive the

original rollback segment transaction slot.

Both of these situations are discussed below with the series of steps that cause the ORA-01555. In thesteps, reference is made to 'QENV'. 'QENV' is short for 'Query Environment', which can be thought of asthe environment that existed when a query is first started and to which Oracle is trying to attain a readconsistent image. Associated with this environment is the SCN (System Change Number) at that time andhence, QENV 50 is the query environment with SCN 50.

Case 1 - Rollback Overwritten

This breaks down into two cases: another session overwriting the rollback that the current sessionrequires or the case where the current session overwrites the rollback information that it requires. Thelatter is discussed in this article because this is usually the harder one to understand.

1. Session 1 starts query at time T1 and QENV 502. Session 1 selects block B1 during this query3. Session 1 updates the block at SCN 514. Session 1 does some other work that generates rollback information.5. Session 1 commits the changes made in steps '3' and '4'. (Now other transactions are free to

overwrite this rollback information)6. Session 1 revisits the same block B1 (perhaps for a different row).

Now, Oracle can see from the block's header that it has been changed and it is later than the requiredQENV (which was 50). Therefore we need to get an image of the block as of this QENV.

If an old enough version of the block can be found in the buffer cache then we will use this, otherwise weneed to rollback the current block to generate another version of the block as at the required QENV.

It is under this condition that Oracle may not be able to get the required rollback information becauseSession 1's changes have generated rollback information that has overwritten it and returns the ORA-1555 error.

Case 2 - Rollback Transaction Slot Overwritten

8/8/2019 ORA-01555

6/9

1. Session 1 starts query at time T1 and QENV 502. Session 1 selects block B1 during this query3. Session 1 updates the block at SCN 514. Session 1 commits the changes (Now other transactions are free to overwrite this rollback

information)5. A session (Session 1, another session or a number of other sessions) then use the same rollback

segment for a series of committed transactions.These transactions each consume a slot in the rollback segment transaction table such that iteventually wraps around (the slots are written to in a circular fashion) and overwrites all the slots.Note that Oracle is free to reuse these slots since all transactions are committed.

6. Session 1's query then visits a block that has been changed since the initial QENV wasestablished. Oracle therefore needs to derive an image of the block as at that point in time.

Next Oracle attempts to lookup the rollback segment header's transaction slot pointed to by the top of thedata block. It then realises that this has been overwritten and attempts to rollback the changes made tothe rollback segment header to get the original transaction slot entry.

If it cannot rollback the rollback segment transaction table sufficiently it will return ORA-1555 since Oraclecan no longer derive the required version of the data block.

It is also possible to encounter a variant of the transaction slot being overwritten when using blockcleanout. This is briefly described below:

Session 1 starts a query at QENV 50. After this another process updates the blocks that Session 1 willrequire. When Session 1 encounters these blocks it determines that the blocks have changed and havenot yet been cleaned out (via delayed block cleanout). Session 1 must determine whether the rows in theblock existed at QENV 50, were subsequently changed.

In order to do this, Oracle must look at the relevant rollback segment transaction table slot to determinethe committed SCN. If this SCN is after the QENV then Oracle must try to construct an older version ofthe block and if it is before then the block just needs clean out to be good enough for the QENV.

If the transaction slot has been overwritten and the transaction table cannot be rolled back to a sufficientlyold enough version then Oracle cannot derive the block image and will return ORA-1555.

Note: Normally Oracle can use an algorithm for determining a block's SCN during block cleanout evenwhen the rollback segment slot has been overwritten. But in this case Oracle cannot guarantee that theversion of the block has not changed since the start of the query.

Solutions

This section lists some of the solutions that can be used to avoid the ORA-01555 problems discussed inthis article. It addresses the cases where rollback segment information is overwritten by the same sessionand when the rollback segment transaction table entry is overwritten.

It is worth highlighting that if a single session experiences the ORA-01555 and it is not one of the specialcases listed at the end of this article, then the session must be using an Oracle extension wherebyfetches across commits are tolerated. This does not follow the ANSI model and in the rare cases whereORA-01555 is returned one of the solutions below must be used.

Case 1 - Rollback Overwritten

8/8/2019 ORA-01555

7/9

1. Increase size of rollback segment which will reduce the likelihood of overwriting rollbackinformation that is needed.

2. Reduce the number of commits (same reason as 1).3. Run the processing against a range of data rather than the whole table. (Same reason as 1).4. Add additional rollback segments. This will allow the updates etc. to be spread across more

rollback segments thereby reducing the chances of overwriting required rollback information.5. If fetching across commits, the code can be changed so that this is not done.6. Ensure that the outer select does not revisit the same block at different times during the

processing. This can be achieved by:o Using a full table scan rather than an index lookupo Introducing a dummy sort so that we retrieve all the data, sort it and then sequentially

visit these data blocks.

Case 2 - Rollback Transaction Slot Overwritten

1. Use any of the methods outlined above except for '6'. This will allow transactions to spread theirwork across multiple rollback segments therefore reducing the likelihood or rollback segmenttransaction table slots being consumed.

2. If it is suspected that the block cleanout variant is the cause, then force block cleanout to occur

prior to the transaction that returns the ORA-1555. This can be achieved by issuing the followingin SQL*Plus, SQL*DBA or Server Manager :

alter session set optimizer_goal = rule;select count(*) from table_name;

If indexes are being accessed then the problem may be an index block and clean out can be forced byensuring that all the index is traversed. Eg, if the index is on a numeric column with a minimum value of25 then the following query will force cleanout of the index:

select index_column from table_name where index_column > 24;

Examples

Listed below are some PL/SQL examples that can be used to illustrate the ORA-1555 cases given above.Before these PL/SQL examples will return this error the database must be configured as follows:

y Use a small buffer cache (db_block_buffers).REASON: You do not want the session executing the script to be able to find old versions of theblock in the buffer cache which can be used to satisfy a block visit without requiring the rollbackinformation.

y Use one rollback segment other than SYSTEM.REASON: You need to ensure that the work being done is generating rollback information thatwill overwrite the rollback information required.

y Ensure that the rollback segment is small.

REASON: See the reason for using one rollback segment.

Rollback Overwritten

rem * 1555_a.sql - Example of getting ora-1555 "Snapshot too old" byrem * a session overwriting the rollback information requiredrem * by the same session.drop table bigemp;create table bigemp (a number, b varchar2(30), done char(1));drop table dummy1;

8/8/2019 ORA-01555

8/9

create table dummy1 (a varchar2(200));rem * Populate the example tables.beginfor i in 1..4000 loopinsert into bigemp values (mod(i,20), to_char(i), 'N');if mod(i,100) = 0 theninsert into dummy1 values ('ssssssssssss');commit;

end if;end loop;commit;end;/rem * Ensure that table is 'cleaned out'.select count(*) from bigemp;declare-- Must use a predicate so that we revisit a changed block at a different-- time.-- If another tx is updating the table then we may not need the predicatecursor c1 is select rowid, bigemp.* from bigemp where a < 20;

beginfor c1rec in c1 loopupdate dummy1 set a = 'aaaaaaaa';update dummy1 set a = 'bbbbbbbb';update dummy1 set a = 'cccccccc';update bigemp set done='Y' where c1rec.rowid = rowid;commit;

end loop;end;/

Rollback Transaction Slot Overwritten

rem * 1555_b.sql - Example of getting ora-1555 "Snapshot too old" byrem * overwriting the transaction slot in the rollbackrem * segment header. This just uses one session.drop table bigemp;create table bigemp (a number, b varchar2(30), done char(1));rem * Populate demo table.beginfor i in 1..200 loopinsert into bigemp values (mod(i,20), to_char(i), 'N');if mod(i,100) = 0 thencommit;

end if;end loop;commit;end;/drop table mydual;create table mydual (a number);insert into mydual values (1);commit;rem * Cleanout demo table.select count(*) from bigemp;declare

8/8/2019 ORA-01555

9/9

cursor c1 is select * from bigemp;begin-- The following update is required to illustrate the problem if block-- cleanout has been done on 'bigemp'. If the cleanout (above) is commented-- out then the update and commit statements can be commented and the-- script will fail with ORA-1555 for the block cleanout variant.update bigemp set b = 'aaaaa';commit;for c1rec in c1 loopfor i in 1..20 loopupdate mydual set a=a;commit;

end loop;end loop;end;/

Special Cases

There are other special cases that may result in an ORA-01555. These are given below but are rare andso not discussed in this article:

y Trusted Oracle can return this if configured in OS MAC mode. DecreasingLOG_CHECKPOINT_INTERVAL on the secondary database may overcome the problem.

y If a query visits a data block that has been changed by using the Oracle discrete transactionfacility then it will return ORA-01555.

y It is feasible that a rollback segment created with the OPTIMAL clause may cause a query toreturn ORA-01555 if it has shrunk during the life of the query causing rollback segmentinformation required to generate consistent read versions of blocks to be lost.

Summary

This article has discussed the reasons behind the error ORA-01555 "Snapshot too old", has provided alist of possible methods to avoid the error when it is encountered, and has provided simple PL/SQLscripts that illustrate the cases discussed.