Re: Help: Rollbacks Keep Blowing Up-v7.1.4

In article <3grp5p$c4k_at_lucy.infi.net>, bhunger_at_richmond.infi.net (Bill Hungerford) writes:
|>Parameters: 130 gig db, 4 rollback segments (each about 1 gig)
|> System:Sun Sparc 1000 running under Sun Solaris 2.3
|>
|>Multiple SQL scripts running on system are blowing up due to rollback
|>segments ("snapshot too old").
|>
|>
|>Also, it appears that when these jobs run, the rollback segments aren't
|>going into extents.
|>
|>Any help on this headache will be *greatly appreciated.
|>
|>Bill Hungerford
|>bhunger_at_richmond.infi.net
|>(804)288-8827
|>

Bill,

The snapshot too old message means that someone is updating this data while the queries are running. Here's the scenario:

1. USER-A starts a long running query.
2. USER-B updates some data, the previous values of this data are in a rollback segment. USER-B commits her updates, this releases the rollback segment.
3. USER-C updates some data (any data) and reuses the rollback segment that USER-B freed up when she committed.
4. Now USER-A wants to see a read-consistent view of the data that was updated by USER-B, and since USER-A started the query before USER-B updated the data, USER-A wants to see the previous values. These previous values were overwritten by USER-C, after USER-B commited and released the rollback segment. Got that?

The real way to prevent this is to run the long-running queries after-hours when the data is not being updated. A way to reduce the likelyhood of this happening is to increase the number of extents in each rollback segment (you didn't say how many extents in each segment) and/or the number of rollback segments. This reduces the likelyhood of USER-C over writing the same rollback segment extent that held USER-B's previous values.

-- 
Jennifer Corliss is on an InterGalactic cruise...       |The opinions expressed 
                                                        |above may not be those
   ...in her office                                     |of my employer.