Rollback segment additions

[rogerF]

--It appears that before you create a non-system rollback segment that you must create a 2nd system rollback segment which can be eliminated after you create the non-system rollback segment

Is this correct ?

If so ... My Db which I inherited already went through this process at some point :-)

If I have a non-system rollback segment than its OK to go ahead and create a 2nd non-system ???

I've done a check and gotten a contention level of over the legendary 1% point that is mentioned in all the docs- although my customers are not complaining .:

=========================
ROLLBACK SEGMENT CONTENTION
=========================

If any ratio is > .01 then more rollback segments are needed

NAME WAITS GETS Ratio
------------------------------ ---------- ---------- ---------
SYSTEM 0 3408 00.00000
RBS01 395 32462 00.01217

2 rows selected.


Overall gets .v. waits...

GETS - # of gets on the rollback segment header: 35872
WAITS - # of waits for the rollback segment header: 395
The ratio of Rollback waits/gets is 1.1%

NB If ratio is more than 1%, create more rollback segments
----------------------------------------------

So ... is it so easy that I just create another non-system rollback segment that has the same features os RBS01 ??

Should I wait until people complain ?

Thanks,

Roger

[Halo]

You can have 1 or 2 rbs in the system ts and atleast 4 rbs in the RBS ts. #rbs required = #users/transactions_per_rbs --> this should be rounded to the next higher multiple of 4. Do not wait for users to complain.

[rogerF]

hmmm ....

I didn't realize that you should/would create more rollback segments in the same tablespace that was used for the first non-system rollback segment. Is it a benefit to create another tablespace dedicated for the 2nd non-system rollback segment ?

What about increasing the size of the tablespace used by the original rollback segment ? Would that be a substitute for created the 2nd one ?

Yes- I have read a bit about rollback segments- it's still a bit difficult.

Thanks,

Roger

[Halo]

You need not create another ts for the new rbs; you can create it in the same rbs. The size of the ts would depend upon the # rbs and their sizes (based upon the transaction size --> which depends upon if the db is OLTP or DW/DSS) you have in the ts. For now, resize the rbs dbf to say 0.5 to 1 gb. Turn on Autoextend, and create the rbs with an optimal storage parameters.
-
Ex. if optimal = (say) 10Mb, then set initial_extent = next_extent = 1/2 of optimal size. In this case, it would be 5Mb. Remember that extent allocation (esp for rbs) is CPU intensive.

[rogerF]

Halo:

Thanks for the tips !

The thing is that the "Rational" software script created the original rollback :-) I didn't work here at that time. Here is the creation command:


CREATE TABLESPACE ROLLBACK
DATAFILE '/u03/oradata/rational/REQPRO_ROLLBACK.dbf' SIZE 10485760 REUSE
DEFAULT STORAGE (INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 ) ONLINE;
---------------------------
Here is the current size:

ROLLBACK /u04/oradata/cqdbnew/rollback.dbf 140Megabyes
-----

I guess that 140M is too little- Could I avoid adding a new rollback seg by just making rollback.dbf larger ?
I prefer less changes to the production DB.

Thanks,

Roger

[Halo]

The initial and next extents should be of the same size. Though you do not have much contection, it is always advisable to have atleast 4 rbs. You cannot substitute the size of the dbf with the # rbs.

[rogerF]

Case closed :-)

-Thanks