Question Details [ID: 275215756923]
              
             Print 
             Help 

         QuestionsSearch/Archives

        Search  Archives  [ Question Details ]  

        
                    
                    
            Jane -- Thanks for the question regarding "snapshot too old error", 
            version oracle 8i

            You Asked

Tom,
Would you tell me what snapshot too old error. When does it 
happen? What's the possible causes? How to fix it?

Thank you very much.

Jane


            and we said...

I think support note <Note:40689.1> covers this topic very well:

ORA-01555 "Snapshot too old" - Detailed Explanation
===================================================

Overview
~~~~~~~~

This article will discuss the circumstances under which a query 
can return the Oracle error ORA-01555 "snapshot too old 
(rollback segment too small)". The article will then proceed to 
discuss actions that can be taken to avoid the error and finally 
will provide some simple PL/SQL scripts that illustrate the 
issues discussed.

Terminology
~~~~~~~~~~~

It is assumed that the reader is familiar with standard Oracle 
terminology such as 'rollback segment' and 'SCN'. If not, the 
reader should first read the Oracle Server Concepts manual and 
related Oracle documentation.

In addition to this, two key concepts are briefly covered below 
which help in the understanding of ORA-01555:
 
1. READ CONSISTENCY:
====================

This is documented in the Oracle Server 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 it guarantees 
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. This obviously visits a large 
number of database blocks to make the change to the data. When 
the user commits the transaction Oracle does NOT go back and 
revisit these blocks to make the change permanent. It is left 
for the next transaction that visits any block affected by the 
update to 'tidy up' the block (hence the term 'delayed block 
cleanout'). 
 
Whenever Oracle changes a database block (index, table, cluster) 
it stores a pointer in the header of the data block which 
identifies the rollback segment used to hold the rollback 
information for the changes made 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 
which indicates that it has been changed at some point. The 
database needs to confirm whether the change has been committed 
or whether it is currently uncommitted. To do this, Oracle 
determines the rollback segment used for the previous 
transaction (from the block's header) and then determines 
whether the rollback 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 subsequent accesses 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 in updating 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 active
              transactions to a rollback
              segment (the 'tx' part), and the rollback segment 
              header has a table that stores information 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. (Since we are 
              free to overwrite committed transactions.)

Data Block 500             Rollback Segment Header 5
+----+--------------+      +----------------------+---------+
| tx | None         |      | transaction entry 01 |ACTIVE   |
+----+--------------+      | transaction entry 02 |ACTIVE   |
| row 1             |      | transaction entry 03 |COMMITTED|
| row 2             |      | transaction entry 04 |COMMITTED|
| ... ..            |      |     ...     ...   .. |  ...    |
| row n             |      | transaction entry nn |COMMITTED|
+-------------------+       +--------------------------------+

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

Data Block 500             Rollback Segment Header 5
+----+--------------+     +----------------------+---------+
| tx |5.3uncommitted|-+   | transaction entry 01 |ACTIVE   |
+----+--------------+ |   | transaction entry 02 |ACTIVE   |
| row 1             | +-->| transaction entry 03 |ACTIVE   |
| row 2 *changed*   |     | transaction entry 04 |COMMITTED|
| ... ..            |     |     ...     ...   .. |  ...    |
| row n             |     | transaction entry nn |COMMITTED|
+------------------+      +--------------------------------+

 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.

Data Block 500                   Rollback Segment Header 5
+----+--------------+       +----------------------+---------+
| tx |5.3uncommitted|--+    | transaction entry 01 |ACTIVE   |
+----+--------------+  |    | transaction entry 02 |ACTIVE   |
| row 1             |  +--->| transaction entry 03 |COMMITTED|
| row 2 *changed*   |       | transaction entry 04 |COMMITTED|
| ... ..            |       |     ...     ...   .. |  ...    |
| row n             |       | transaction entry nn |COMMITTED|
+------------------+        +--------------------------------+

 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
              table slot, sees that it has been committed, and
              changes data block 500 to reflect the
              true state of the datablock. (i.e. it performs
              delayed cleanout).

Data Block 500                   Rollback Segment Header 5
+----+--------------+      +----------------------+---------+
| tx | None         |      | transaction entry 01 |ACTIVE   |
+----+--------------+      | transaction entry 02 |ACTIVE   |
| row 1             |      | transaction entry 03 |COMMITTED|
| row 2             |      | transaction entry 04 |COMMITTED|
| ... ..            |      |     ...     ...   .. |  ...   |
| row n             |      | transaction entry nn |COMMITTED|
+------------------+       +--------------------------------+


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 :

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

  o The transaction slot in the rollback segment's transaction 
table (stored in the rollback segment's header) 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 the steps, reference is made 
to 'QENV'. 'QENV' is short for 'Query Environment', which can be 
thought of as the environment that existed when a query is first 
started and to which Oracle is trying to attain a read 
consistent image. Associated with this environment is the SCN
(System Change Number) at that time and hence, 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 session requires or the case where 
the current session  overwrites the rollback information that it 
requires. The latter is discussed in this article because this 
is usually the harder one to understand.

  Steps:

    1. Session 1 starts query at time T1 and QENV 50

    2. Session 1 selects block B1 during this query

    3. Session 1 updates the block at SCN 51

    4. 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 required QENV (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 we need 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 because Session 1's 
changes have generated rollback information that has overwritten 
it and returns the ORA-1555 error.

 CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN

    1. Session 1 starts query at time T1 and QENV 50

    2. Session 1 selects block B1 during this query

    3. Session 1 updates the block at SCN 51

    4. 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 it eventually 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 was established. 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 the data 
block. It then realises that this has been overwritten and 
attempts to rollback the changes made to the 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 Oracle can 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 block cleanout. This is 
briefly described below :

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

 In order to do this, Oracle must look at the relevant rollback 
segment transaction table slot to determine the committed SCN. 
If this SCN is after the QENV then Oracle must try to construct 
an older version of the 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 sufficiently old 
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 even when the rollback segment 
slot has been overwritten. But in this case Oracle cannot 
guarantee that the version 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 in this article. It 
addresses the cases where rollback segment information is 
overwritten by the same session and 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 special cases listed at 
the end of this article, then the session must be using an 
Oracle extension whereby fetches across commits are tolerated. 
This does not follow the ANSI model and in the rare cases where 
ORA-01555 is returned one of the solutions below must be used.

 CASE 1 - ROLLBACK OVERWRITTEN

  1.  Increase size of rollback segment which will reduce the 
likelihood of overwriting rollback information 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 :

        - Using a full table scan rather than an index lookup
        - 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 their work across multiple 
rollback segments therefore reducing the likelihood or rollback 
segment transaction 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 following in 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 by ensuring that all the 
index is traversed. Eg, if the index is on a numeric column with 
a minimum value of 25 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 :

  o 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 the block in the buffer cache 
which can be used to satisfy a block visit without requiring the 
rollback information.

  o Use one rollback segment other than SYSTEM.

    REASON: You need to ensure that the work being done is 
generating rollback information that will overwrite the rollback 
information required.

  o Ensure that the rollback segment is small.

    REASON: See the reason for using one rollback segment.

 ROLLBACK OVERWRITTEN

rem * 1555_a.sql - 
rem * Example of getting ora-1555 "Snapshot too old" by
rem * session overwriting the rollback information required
rem * by the same session. 

  drop table bigemp;
  create table bigemp (a number, b varchar2(30), done char(1));

  drop table dummy1; 
  create table dummy1 (a varchar2(200));

  rem * Populate the example tables.
  begin
   for i in 1..4000 loop
     insert into bigemp values (mod(i,20), to_char(i), 'N');
     if mod(i,100) = 0 then
       insert 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 predicate
   cursor c1 is select rowid, bigemp.* from bigemp where a < 20;

  begin
   for c1rec in c1 loop

     update 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" by
  rem *              overwriting the transaction slot in the 
rollback
  rem *              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.
  begin
   for i in 1..200 loop
     insert into bigemp values (mod(i,20), to_char(i), 'N');
     if mod(i,100) = 0 then
       commit;
     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

   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 loop
     for i in 1..20 loop
       update 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 and so not discussed in this 
article :

 o Trusted Oracle can return this if configured in OS MAC mode. 
Decreasing LOG_CHECKPOINT_INTERVAL on the secondary database may 
overcome the problem.

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

 o It is feasible that a rollback segment created with the 
OPTIMAL clause maycause a query to return ORA-01555 if it has 
shrunk during the life of the query causing rollback segment 
information 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 a list of possible 
methods to avoid the error when it is encountered, and has 
provided simple PL/SQL scripts that illustrate the cases 
discussed.

          



                    
            Was this response helpful to you? Let us know!   Reviews   
                  Snapshot too Old.  March 20, 2001 
                  Reviewer:  Ganesh Raja  from Chennai, Tamil Nadu India. 
                  This Article Was real Good. Thanks Tom for Highlighting this. 

                  Snapshot too old error  March 29, 2001 
                  Reviewer:  KP  from India 
                  yr reply is ultimate. 




            Bookmark this page with the link HERE 
                        Information 


                               This page provides the details of the question 
                              asked. To find another question click on the 
                              search tab. To view the question archives by week 
                              click on the archives tab. 








       
      Copyright  2001 Oracle Corporation, All rights reserved. 


