Rollback segment additions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Rollback segment additions

  1. #1
    Join Date
    Nov 2000
    Posts
    89
    --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

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  3. #3
    Join Date
    Nov 2000
    Posts
    89
    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


  4. #4
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  5. #5
    Join Date
    Nov 2000
    Posts
    89
    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


  6. #6
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  7. #7
    Join Date
    Nov 2000
    Posts
    89
    Case closed :-)

    -Thanks


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width