We have rollback contention determined after queyring v$rollname and v$rollstat.
We are showing rollback segments as following:
RBS01 INITIAL =524288
MIN EXTENTS= 8
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?
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?
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
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
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.
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.