Re: odd number of rollback segments

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.

IMHO -- Mark D Powell -- Received on Fri Sep 01 2006 - 10:45:05 CDT