Re: snapshot too old

In article <3kq6hc$ar4_at_hettar.cv.com>, dano_at_bluedevil.cv.COM (Dan Weinman) wrote:

> I have a pro*c program that is used to update selected records in a
 large table.
> After processing about 400,000 records the program aborts with the
> ORA-01555 error. The program declares a cursor for the select statement
> and then fetches 500 rows at a time using the cursor. It then has the
> possibility of updating any or all of the rows depending on the values of
> the columns selected. A commit is done after the update and another 500
> rows is fetched. This will continue until the error occurs.
>
> I know that the rollback segments hold the read consistent view of the data
> being used by the cursor and the updated data for rollback purposes. The
> database has four rollback segments configured.
>
> Is the problem not enough rollback segments? I did not see any new extents
> allocated when watching the rollback activity with monitor.
>
> Any information on this would be appreciated.
>
> Thanks in advance.
>
> Dan

I've run into this problem before (years ago). SNAPSHOT TOO OLD occurs when someone has overwritten the rollback data required to complete your query-consistent SELECT. As I understand it (and my information MAY be out of date), generally, this happens only when there are lots of users on the system, and the users make changes (creating rollback data in the rollback segments) and do COMMITs to data which is concurrently being queried in a query-consistent SELECT. Since they've COMMITed the data, the rollback segment blocks may be reused, even though they may still be necessary to complete the query. Oracle's philosophy (since day one) has been that updates are of a higher priority than queries; therefore if an UPDATE wants to overwrite rollback data (potentially) required only by a read-consistent query, it will do so... and the query can eventually get a SNAPSHOT TOO OLD. In your case, you are probably overwriting your own rollback info. Every time you change a row THAT HAS NOT BEEN WRITTEN TO WITHIN THE CURRENT TRANSACTION, rollback data needs to be written to the rollback segment. There is no need to write this information again after the first write within the transaction, since if you rollback, you need to go to the last COMMITted data, not the data just prior to your last change. Since you are COMMITting every 500 rows, you are freeing the rollback segment space; it can eventually be overwritten. As your program continues, it is quite possible that it overwrites this data itself, thereby giving you the error.

One way to check if this is indeed the problem is to COMMIT less frequently. I know, I know... I'm usually the first to recommend to make transactions as short as possible (e.g. COMMIT MORE frequently); this seems to be an exception. However, it is quite possible that this will simply change the error returned: instead of getting SNAPSHOT TOO OLD because you overwrote necessary rollback data, you may simply run out of rollback segment space and need to increase that...

Again, be warned... the last time I saw this, the customer was running Oracle V6. It's probably the same now, but I can't guarantee it.

I hope this helps.

Evan

-- 
+---------------------------------------------------------+
| Evan Greene                    Sr. Technical Consultant |
| Stratus Computer, Inc.          WorldWide Sales Support |
| 55 Fairbanks Blvd.          Evan_Greene_at_vos.stratus.com |
| Marlboro, MA  01752                       (508)490-6345 |
+---------------------------------------------------------+