Re: more vs. larger rollbacks

You say that you have created 3 1.5GB rollback segments but the HWM never get above 300M.

I think you have NOT created the rollback segments at 1.5GB.

If, for example, you specify Initial as 100MB, next ad 100MB and min extents as 2, then you have created a 200MB rollback segment. It may be inside a 1.5GB tablespace, but only big transactions cause a rollback segment to extend. Otherwise, Oracle just wraps around and around the current size.

Also, what about OPTIMAL? If it is set to a size less than 1.5GB, Oracle will shrink the rollback segment back to that size.

I suspect that you are getting the ORA-01555 snapshot too old; rollback too small type error. This is not an out-of space error. This means a query needs to find old data in the rollback (for read consistency) but the updates have wrapped around and overwritten the old data. Curiously, if the updates had not committed as often, you might avoid this error! (But then, the updates might extend the rollback segments past the size of the tablespace!)

This

Doug Cowles wrote:

> I know the rule of thumb is, lots of connections and users, OLTP, lots
> of little and
> medium sized rollback segments, big batch jobs, fewer, and larger
> rollback segments.
>
> But..
>
> We have someone doing some preliminary datawarehouse loads, and have
> provided
> 3 1.5G rollback segments, combined equalling 4.5G which is bigger than
> the database
> itself (I know that's ridiculous) , but our user is getting rollback too
> small, and the high
> water marks on these three 1.5G rollback segments never get above 300M.
> Still
> PLENTY of room, at far as I know.
>
> So..
> I'm guessing that there are certain situations in Oracle, even with one
> user and one session,
> where Oracle will ask for another rollback segment, rather than extend
> one where there
> is plenty of room. Can anyone either tell me I'm wrong, or tell me
> what kind of circumstances would cause this?
>
> As to the errors, I have not seen specifically what it is. User says
> it's rollback too small,
> but for all I know it could be need more rollbacks or snapshot too old.
> Regardless..any
> feedback would be appreciated.
>
> - Dc.
Received on Tue May 25 1999 - 17:55:17 CDT