Re: ANOTHER KIND OF SNAPSHOT TOO OLD....

From: Jeremiah Wilton <jeremiah_at_wolfenet.com>
Date: Tue, 2 Mar 1999 09:14:00 -0800
Message-ID: <Pine.OSF.4.02.9903020859390.21287-100000@gonzo.wolfenet.com>

On Tue, 2 Mar 1999 thuge_at_steelcase-strafor.com wrote:
>
> Do you have an optimal option set to this segment ?
> if yes, try to increase it or delete this option.
>
> "LUIS CARLOS LANNOCCA" <lannocca_at_sti.com.br> wrote:
> > I have a large table with 4Gb and every query dealing with a great number of
> > rows
> > send me a ORA-01555 snapshot too old , rollback segment number 2 name roll_1
> > too small ,
> > but only a MyRoll rollback segment is present with 200M . I receive this
> > message even using Set Transaction Use Rollback MyRoll. Only one user is
> > connect to database when its occurs, always doing queries , not inserts or
> > updates or deletes. My database runs in a NT 4.0 P3 , dual processor with
> > 256Mb .

"ORA-01555 snapshot too old" cannot be eliminated or alleviated by using the "set transaction use rollback segment ...;" command. The error is the result of your query being unable to create a read-consistent verion of the data you are trying to query.

In Oracle, SQL queries are read-consistent, with respect to a fixed point in time, the time you issued the query. If you start you query at 10:00, and it is still getting data 20 minutes later, it is still showing you the way that data looked at 10:00. This is the case even if people have come in, changed the underlying data, committed and gone away. You still get a read-consistent view of the data.

To accomplish this trick, Oracle looks at each data block to see if it has been modified since your query started. If it has, it looks for the old data in the various rollback segments where the transactions that changed the data stored their "undo" (before-images). Eventually, after a long time, the undo for those transactions gets overwritten in the rollback segments, and your long-running query can no longer piece together the way the data looked at the time your query started, This results in ORA-1555.

You can reduce the incidence of ORA-1555 in several ways:

- query data during less active times
- increase the size of all rollback segments
- make your query complete faster (better plan/parallel query)
- eliminate other people using "set transaction use rollback..." This

  thrashes one rollback, increasing the likelihood that CR data will be   overwritten.
- eliminate shrinks of rollback segments. They obliterate data that could   be used to create CR blocks.

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Tue Mar 02 1999 - 11:14:00 CST