Re: newbie DBA - rollback segments

Mark Perrault wrote:
> I'm new to the oracle dba roll, so please be gentle.
>
> I load several large tables every night. My database is only about
> 20gB and is 24x7. I have 10 rollback segments of 100 mB each. I've
> noticed that the I have a rather high amount of that space getting
> used up (especially after I ran an adhoc UPDATE query on one of those
> large tables). How do I clear the RBS (beyond stopping and restarting
> the instance)? Is there a danger in having 75% or more utilization of
> those RBS's?

The rollback segment is a circular buffer. So it'll automatically re-use its space. There is no need to "clear" it. The segment grows usually when you have long running transactions (preventing the rollback segment from re-using certain extents because the transaction has not completed). But when the transaction commits, the space is available for re-use again.

Having a large RBS is not a concern except that for optimal performance, you would want the RBS to be just small enough to support the largest transaction for your apps. This way, the RBS can be cached in the buffer cache and you save disk I/O. To achieve this, set the OPTIMAL parameter in the storage clause of the RBS. This will cause Oracle to attempt to periodically shrink the RBS back to the optimal size you set.   If you do this, be sure to check V$ROLLSTAT regularly and adjust your OPTIMAL parameter so that OPTSIZE is close to AVEACTIVE, and SHRINKS is a low value.

Cheers,
Dave

>
> Thanks,
> Mark
Received on Wed Jul 02 2003 - 15:55:15 CDT