Re: newbie question about RBS

In article <3b20cc63$1_2_at_excalibur.gbmtech.net>, "wonderinguy" <wonderinguy_at_hotmail.com> wrote:

> Folks, I am pretty new to Oracle. I was under the impression that, once
> all the transactions are commited, the roll back segment tablespace
> would go back to 0 % usage + overhead. But it keeps growing and is
> getting close to 100 %. Is this normal ? Or do I have to do something to
> reset it.
>

Not quite how it works.....Remember that Oracle wants the data in the rollback to last as long as possible (even for transactions that have already committed) for read consistency purposes.

So, if you do not set an OPTIMAL parameter for your rollback segment, it will grow and grow. This is great for read consistency, cause it can roll back all the transactions and see snapshots of the data for any point in time.

The downside is that rollback space is finite. This approach will quickly fill the rollback segment space, and then DMLs will begin to fail.

The other extreme would be as soon as a transaction committed, it released all the rollback it used, and the next transaction came along and overwrote it. This would make it very unlikely that you'd ever run out of space in the rollback segment, but, it would also make it nearly impossible to get a read consistent view of the data on a system w/ even low levels of DML activity.

So, the compromise is the OPTIMAL parameter of the rollback segment. If it's set, then at certain times, Oracle will attempt to shrink the rollback segment to the OPTIMAL size and release extents allocated to the segment that are in excess of OPTIMAL. The events that cause this event to occur are:

• a transaction can't allocate more space
• SMON will wake up every 125 minutes (or is it 120 now?) - the DBA issues alter rollback segment rbs shrink

When the shrink event occurs, Oracle attempts to deallocate extents that do not have any active transactions.

In this way, Oracle strikes a balance between efficient use of disk resources and it's ability to do read consistency.

Hope that helps,
-Mark Received on Sat Jun 09 2001 - 02:14:40 CDT