snapshot too old
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: snapshot too old

  1. #1
    Join Date
    Aug 2000
    Posts
    163
    how does 'sanpshot too old' error gets generated?
    It is my understanding that it has something to do with rollback segment which doesn't exist any more. Can anyone give me a little more detail? Thank you.

  2. #2
    Join Date
    Sep 2000
    Posts
    96
    A 'snapshot too old' msg is received if the Oracle server can't construct a read-consistent image of data. This error is caused when the transaction that made the change has already committed and: the transaction slot in the rollback header has been reused or the before-image in the rollback segment has been overwritten by another transaction.

    This problem can be minimized by ensuring the rollback segments are created with higher MINEXTENTS, larger extent sized, higher OPTIMAl value.

  3. #3
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    Note from metalink :

    Doc ID: Note:10630.1
    Subject: ORA-01555: "Snapshot too old" - Overview
    Type: BULLETIN
    Status: PUBLISHED
    Content Type: TEXT/PLAIN
    Creation Date: 29-APR-1993
    Last Revision Date: 24-MAR-2000
    Language: USAENG


    PURPOSE
    This article explains possible causes of the Oracle error ORA-01555.

    SCOPE & APPLICATION
    Users requiring an overview of the causes of the Oracle error ORA-01555.


    There are various reasons why customers can get the error ORA-01555. Sometimes
    it is due to rollback segments being too small in size, but there are other
    reasons. This bulletin is an attempt to give a complete 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 a single query is consistent with respect to time
    when the query began. Therefore, a query never sees the data-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 produce read-consistency, Oracle
    marks the current SCN as the query enters the execution phase. The query can
    only 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 a transaction makes any changes, a snapshot of the
    record before the changes were made is copied to a rollback segment and the
    data block header is marked appropriately with the address of the rollback
    segment block where the changes are recorded. The data block also maintains
    the SCN of the last committed change to the block.

    As the data blocks are read on behalf of the query, only blocks with lower
    SCN than the query SCN will be read. If a block has uncommitted changes of
    other transactions or changed data with more recent SCN, then the data is
    reconstructed using the saved snapshot from the rollback segments. In some
    rare situations, if RDBMS is not able to reconstruct the snapshot for a long
    running query, the query results in an 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 current SCN 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 rollback segment information could not be found becuase
    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 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 this error.

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


    Hope this helps
    Gert

  4. #4
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    You can also look at the Oracle DBA Handbook by Oracle Press page 221.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Or check out [url]http://osi.oracle.com/wa/ask/owa/ask_tom_pkg.display?p_dispid=275215756923[/url]
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width