Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Rollback segment error

Re: Rollback segment error

From: <markp7832_at_my-deja.com>
Date: Fri, 17 Dec 1999 17:35:34 GMT
Message-ID: <83ds96$1j5$1@nnrp1.deja.com>


In article <s5jo34alqrs184_at_corp.supernews.com>,   "Ari Kaplan" <akaplan_at_interaccess.com> wrote:
> The problem is that your rollback segments are too small. The ORA-1555
> really has to do with rollback segments ; "snapshot" is a misnomer.
Increase
> the NEXT size for your rollback segments.
>
> Best regards,
>
> -Ari Kaplan
> Independent Oracle DBA
> For 350 Oracle tips, visit http://homepage.interaccess.com/~akaplan
>
>




> =======
> ckchan5_at_my-deja.com wrote in message <83ck26$57t$1_at_nnrp1.deja.com>...
> >I have the following error during select data from the table process.
> >
> >ERROR:
> >ORA-01555: snapshot too old: rollback segment number 1 with
name "R05"
> >too small
> >
> >I tried to fine tune the sql statement, but still getting the same
> >error message.
> >
> >Is there any way I can overcome this error without modify my existing
> >sql statement.
> >
> >Regards
> >

The original post does not provide enough detail on the SQL receiving the error or the current allocation of the rbs segments to provide an precise answer.

Maybe the rollback segments are too small or maybe the data being read is in a table that is heavily updated by single row, immediately commited transactions. Or perhaps the SQL in question is reading and updating the same table with commits so increasing the size of the rollback segments will not help at all.

In any case it is Oracle's recommendation that the initial and next extent sizes should match for rbs segments. It would be better to drop and recreate them if the extent size needs changing.

Here is an Oracle white paper on the subject of 1555:

Article-ID:         <Note:10630.1>          Revision: 0          PUBLIC
Circulation:        ** PUBLIC **
Creator:            OLSBB
Folder:             server.dbms.dba
Topic:              ** Managing Rollback Segments
:Subject:            Ora 1555:Snapshot too old
Modified:           11 Sep 94 10:09:10
:Attachments:        NONE


Document:103220.525
Updated:29-APR-93
Category:RDBMS

   ORA 1555 : SNAPSHOT TOO OLD


  Authors: Chitra Mitra, Ziyad Dahbour, Rama Velpuri

There are various reasons why customers can get the error ORA 1555. Sometimes its 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 1555 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 ORA 1555 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 1555 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 1555 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 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 ORA 1555 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 need 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 ORA 1555 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, lets 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 1555 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. Accoding 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 1555.

---
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 17 1999 - 11:35:34 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US