Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: rollback segment too small
Njål A. Ekern wrote:
>
> Ong Chin Hui wrote:
> >
> > On Sat, 13 Jun 1998 05:40:38 GMT, "rok" <rok_at_MCI2000.com> wrote:
> >
> > Thanks for responding
> >
> > 1. Forget to mention one thing. I perform a commit after every
> > update/insert in the select.
>
> I think that explains it. I read somewhere that you may erronously
> receive a snapshot_too_old-message when your job is doing many small
> updates and commits.
Searching in Deja News, I found that newsgroup-article. The interesting part is number 4 at the bottom, since tou read from tableA and update in tableB.
:Subject: Re: Rollback segment too small (-1555) :From: Sean Walters <sean.walters_at_pancredit.com> :Date: 1996/05/08 :Message-ID: <3191065F.123A_at_pancredit.com> :Newsgroups: comp.databases.oracle :[Subscribe to comp.databases.oracle]
Stephen, Kirk wrote:
>
> Often when extracting large volumes of data from an Oracle table I recieve the message:-
>
> Rollback segment too small -1555
>
> This occurs even though I'm doing a select against the table and there are no other processes updating the table and activity on the
> database as a whole is light. We have 8 rollback segments each 1/2 gig in size. How can this be and what can I do to prevent it ? Is
> there a FAQ out there somewhere which addresses this issue ?
>
> Thanks
>
> Stephen Kirk
Stephen,
Your problem sounds similar to one I have experienced. The following Oracle technical note may be of use.
Regards,
Sean Walters
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
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.Received on Tue Jun 30 1998 - 04:38:52 CDT