Re: How to manage rolback segments?

I do not believe that the SELECT statements are writing rollback entries. On the contrary, SELECT statements read from rollback segments -- this is how Oracle achieves its non-blocking read consistency.

You didn't give the actual error, but I'll bet it was ORA-01555: snapshot too old (rollback segment too small)

This happens when a row your query needs was updated and committed AFTER your query started but BEFORE your query has actually read it yet. Oracle recognizes this and goes to the rollback segment to re-construct the row as it was at the start of your query. If the rollback has wrapped around (due to a lot of small transactions), Oracle can no longer find the undo image needed.

V$ROLLSTATS counts the time each rollback wraps around. Monitor this during active updating. If the time to wrap around is less than the query time, this error come up.

You have set OPTIMAL to 20 MB. However, with MINEXTENTS = 2, the segments may have not yet expanded past two (or a few) 200 KB extents. If you really need 20MB OPTIMAL, re-create the segments with MINEXTENTS = 100.

HTH EBR wrote:

> Hi John,
>
> Thank you for all of your precious input and sorry for the mismatched
> information, in fact I had one configuration when I sent the first
> message and changed when I sent the second.
>
> I'm now trying to configure a pool of 8 rollback segments in a
> tablespace ROLLBACK_DATA. The tablespace is now 2GB and each rollback
> segment has the following settings:
>
> MINEXTENTS: 2
> MAXEXTENTS: 2000
> INITIAL: 200K
> NEXT: 200K
> OPTIMAL: 20M
>
> However, I'm still getting messages of "cannot extend rollback,
> snapshot too old, segment too small". As far as I noticed, select
> statements are generating rollback segments, and I supposed only
> INSERTs, UPDATEs and DELETEs did so.
>
> Our queries act on large selections, sometimes more than 8 million
> records at a time. Shoud I have rollback segments as large as the
> database?
>
> Even a single SELECT COUNT(ROWID) FROM TABLE is generating a rollback
> error. Is it because the table is too large?
>
> Any suggestions would be very appreciated.
>
> On Mon, 05 Apr 1999 20:56:15 -0500, John Higgins <JH33378_at_deere.com>
> wrote:
>
> >Maybe I don't get it or maybe you made a typo, but:
> >If the 5 rollback segments are only 20 MB then they have not yet grown as
> >big as the 50 MB OPTIMAL.
> >
> >If you meant that the OPTIMAL is 50 K, then here is an answer: you have set
> >MINEXTENTS to 2, so each rollback segment must have a 10 K initial extent
> >and a 300 K next extent. At some time after a transaction caused a rollback
> >to extend, it would have had the 10K initial extent and some number of 300K
> >extents. When the shrink occurs, it cannot go below 2 MINEXTENTS. In
> >addition, it is possible that the 10K extent is among those given back --
> >then the MINEXTENTS=2 would mean 2 extents of 300K.
> >
> >HTH
>
> EBR
> ** Please remove the primes to get my real e-mail
Received on Tue Apr 06 1999 - 21:46:21 CDT