ROLLBACK SEGMENTS

[Ablakios]

Hi all
We have rollback contention determined after queyring v$rollname and v$rollstat.
We are showing rollback segments as following:
RBS01 INITIAL =524288
NEXT =524288
MIN EXTENTS= 8
MAX EXTENT=32765
AND no OPTIMAL specified.

I would like to increase the size of the INITIAL & NEXT and set MINEXTET to 20.
Our db_block_size is 8196.

Can I increase initial and next by just multiplying their respective value to 2 to have large rollback segments?
EX= INITIAL =(524288 x2)=1048576
NEXT =(524288x2)= 1048576
MINEXTENT = 20
MAXEXTENT =??????? (Considering the figure what value may give to maxextent and Optimal?
OPTIMAL =???????
Total rollback segment online 34.
After creation of all the big rollbacks I will bring them all online and take all the little offline and drop then right?

Please advise

[SANJAY_G]

You can increase the size of initial and next but you will have to drop and recreate the rollbacksegments.
OPTIMAL depend on, to what size you want to maintain your rollback segments to avaiod dynamic allolcation of extents.
Keep in mind..
INITIAL=NEXT and OPTIMAL > MINEXTENTS*INITIAL

Sanjay

[Ablakios]

Sanjay
Thank you for responding so promptly.
Considering the following
INITIAL=NEXT and OPTIMAL > MINEXTENTS*INITIAL

My new rollback segment size will be as follow.
INITIAL= 1048576
NEXT = 1048576
MINEXTENTS = 20
MAXEXTENTS =? (what's the new value?)0ld value=32765

OPTIMAL= 26214400 (ie 1048576*20*1.25)

The value of the orignal maxextent on the small rollback segment is 32765.

IS THERE ANY GUIDELINE TO INCREASE THE SIZE OF MAXEXTENTS?

And once I create the new rollback segments I will first bring them online, then take the old offline and drop them.

Can you create a new rollback segment rbs1, if you already have rbs1? or you first offline rbs1, drop it and then recreate rbs1.

Please advise.

[grjohnson]

I'd keep your MAXEXTENTS to a nominal size depending on the size of your extents, tablespace size and transaction size. Set your MAXEXTENTS from 200 - 2000 if you'd like. In your case it depends on how large you transaction are likely to grow.

The only thing I'd advise you on is to HAVE a MAXEXTENT set, in order to STOP a rouge transactions from occupying the entire RBS TABLESPACE.

Also Setting your OPTIMAL to small (which I'm not saying you have done, but again depends on you average transaction size) can cause "Snapshot too old", because a shrink can occur on your rollback segment and cause another process using the segment to lose it's consistent view. Very frustrating. In my opinion, the ROLLBACK SHOULD NOT shrirk until an active transaction using it for read consistency is completed. (But I'm not developing the oracle engine). Unfortunalty, Oracle9i didn't fix this issue either, istead of setting an OPTIMAL size, you set a RETENTION_TIME. This will cause the same problem.

Sorry I got a bit off track...

[anandkl]

I would avoid the use of OPTIMAL storage parameter as this will lead to dynamic allocation and deallocation of the extents which will lead to low performance.Instead of setting optimal storage parameter,set proper values for INITIAL & NEXT storage.
You can get to know how much of undo information is generated for you transaction by checking the
'USED_UBLK' column of v$transaction after executing the transaction.
This will give u the no of undo blocks used by the transaction.
Multiply this by ur block size and round it to the next multiple of power 2.
Set the result got as the value for the INITIAL and NEXT storage parameter and set ur minextents to 20.

HOPE THIS HELPS.

REGARDS
ANANDKL