Re: odd number of rollback segments

Mark D Powell wrote:
> joel garry wrote:
> > Brian Peasland wrote:
> > > Because someone created the rollback segments different. Either that, or
> > > they have allocated additional extents with no OPTIMAL setting causing
> > > the rollback segment to shrink back to 2M.
> > >
> >
> > Hoping there is a typo there.
> >
> > In general, don't use OPTIMAL, it causes ORA-1555 by shrinking
> > rollbacks at inopportune times. Make all the rollbacks nice and big,
> > big enough for all normal processing, and the same size (except perhaps
> > if you have some known batch processing that makes unusually large
> > transactions, in which case you need to control the assignment manually
> > anyways).
> >
> > jg
> > --
> > @home.com is bogus.
>
> I will disagree on the use of optimal. We used the parameter from when
> it was introduced till version 9.2.0.6 when we starting switching our
> rollback tablespace over to undo tablespaces. By creating "nice and
> big" rollback segments the parameter should not result in many 1555
> errors. If the optimal parameter is not set then uneven DML activity
> by tasks can result in the inefficient use of rollback tablespace free
> space. One rbs segment can fail to extend when the total free space is
> ample. Our rollback segment extent size was 1M to 5M and we set
> minimum extents to 5 to 10 so that on our main db 50M of data had to be
> generated before a segment wrapped around to start over. That meant
> optimal was set to 50M.
>
> If you do not use optimal then RBS segment management is required. The
> key is to set optimal up to a value that does not release no longer
> active extents very frequently but will eliminate the need to manually
> clean up the rbs segment space allocation.
>

I eventually came to the conclusion that if you _can_ use "nice and big" then you might as well use them. For example, create a big rollback tablespace, let the rbs segments extend to a natural size for a while, then create them all that size. The reason this worked better for me is that transactional volume would be increasing, so at some point the optimal would change from helping to hurting. And if you couldn't use "nice and big" for whatever reason, optimal would always wind up hurting. (By hurting, I mean shrinking a rollback segment when it is going to be needed by a long running transaction - I always considered that a database self-inflicted ORA-1555. Hence, the advice that OPTIMAL causes ORA-1555).

I'm sure we can agree to disagree on this, and just be thankful we generally don't have to worry about it anymore. I'd generally take advice from Mark & Brian before my own, anyways.

jg

--
@home.com is bogus.
Not that Mark & Brian!  http://www.955klos.com/markandbrian/