-
--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
-
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.
-
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
-
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.
-
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
-
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.
-
