Re: Rollback Segs

What is your rollback tablespace size? I will assume 2000K. You can adjust the math for your case.

You are creating 10 segments, initial 50K, next 50K, minextents 2. That will use 10 * (50 + 50) = 1000K, leaving 1000K as freespace.

Then, someday, a large transaction causes segment X to consume all the freespace. It does this by allocating another 20 extents of 50K. Without setting OPTIMAL, segment X will retain
all these extents forever -- you then have 9 segments of 2 extents and 1 segment of 22 extents.

Then, someday later, it is time to run another large transaction. If Oracle picks (by luck) the large
rollback, all will be well. But, if Oracle picks one of the other 9 segments, you will get the
'cannot extend .....' error. That's 9 to 1 against you!

The point of setting OPTIMAL is to cause the big rollback segment to give back the extra extents
to freespace. That way any randomly chosen rollback segment can grow from 2 extents to 22 extents
as needed.

As to the maxextents UNLIMITED: The real limit is the size of the rollback tablespace.
If a super transaction needs to expand to 23 or more extents, the freespace will not exist
and you will get the 'cannot extend .....' error. In this case, I would set the maxextents to 22
to remind me there is no such thing as UNLIMITED.

kal121_at_yahoo.com wrote:

> Hi,
>
> I am creating 10 rollback segs, initial extent 50K, next extent 50K.
>
> My question is, is it better to set maxexents UNLIMITED, and set the OPTIMAL
> to some value to provide shrinkage
>
> OR
>
> put a cap on the number maxextents to begin with, eg) maxextents = 250 and run
> the risk of running out of extents on a transaction.
>
> Thanks,
> KL
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Wed Nov 11 1998 - 21:51:21 CST