Solved

Rollbacks and Read Only

Posted on 2001-08-09
2
683 Views
Last Modified: 2009-12-16
I'm trying to spool a report on a large, OLTP, database.  I'm getting a lot of 'snapshot too old' stuff even though I've reduced the # of days I'm requesting down to one from thirty.  

ORA-01555: snapshot too old: rollback segment number 7 with name "R05" too small.

We have a large rollback segment and when I use it, it still breaks as follows:
BASE-DON> SET TRANSACTION USE ROLLBACK SEGMENT large_rbs;

Transaction set.

BASE-DON>
BASE-DON>
BASE-DON> select distinct c.clientnumber,
 .........
             *
ERROR at line 24:
ORA-01555: snapshot too old: rollback segment number 23 with name "R21" too small

When I SET TRANSACTIO READ ONLY, it still breaks.   I'm not understanding what is going
on here.   Can somebody give me a reason this happens?


0
Comment
Question by:DonFreeman
2 Comments
 
LVL 5

Expert Comment

by:ser6398
ID: 6370207
You may find the following helpful (it's a little old, but the info is still valid):

_______________________________________

Subject:            CAUSES AND SOLUTIONS TO ORA-1555 "SNAPSHOT TOO OLD"
Creator:            KQUINN
Modified:           03 Jan 97 05:59:10          [Article Exists In GSX]



CAUSES AND SOLUTIONS TO ORA-1555 "SNAPSHOT TOO OLD"
===================================================

Overview
~~~~~~~~

 This article will discuss the circumstances under which a query can return the
Oracle error 'ORA-1555 "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
Oracle7 Server Concepts manual and related Oracle7 documentation.

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

  This is documented in the Oracle7 Server Concepts manual from page 10-6 to
 10-9 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's ability to have multi-version read consistency is invaluable to the
 database user so that they can guarantee that they are seeing a consistent
 view of the data (no 'dirty reads').

 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. (Ie, 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-1555 Explanation
~~~~~~~~~~~~~~~~~~~~

 There are two fundamental causes of the error ORA-1555 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-1555. 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-1555 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-1555
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-1555 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: We 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: We 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 - Example of getting ora-1555 "Snapshot too old" by
  rem *              a 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-1555. 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 Oracle7
   discrete transaction facility then it will return ORA-1555.

 o It is feasible that a rollback segment created with the OPTIMAL clause may
   cause a query to return ORA-1555 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-1555 "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.


0
 
LVL 1

Accepted Solution

by:
dpd0809 earned 50 total points
ID: 6385633
The other way to do that is to create what I typically call a "BIGRBS".  That means you have a LARGE rollback segment that is normally offline.  Just before you execute your query, you 1) bring the BIGRBS online and then 2) SET TRANSCATION TO BIGRBS.  After your query is run, take the BIG RBS offline. This does 2 things.  One, it ensures you do have a large enough Rollback Segment to work in. Two, it does not compete in the other Rollback Segments. If this does not solve the problem, try writing the data to temporary tables.  Then use a PL/SQL block to gather a finite set of records (have a cursor of maybe 1000 rows) that you can write into the temporary table, committing every 1000 rows.  Once all the data is in, run the report off the temporary table.  Truncate it after use.

Hope this helps.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now