Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: on tablespace and extent size for RBS

RE: on tablespace and extent size for RBS

From: Mark W. Farnham <>
Date: Fri, 18 Jun 2004 08:33:26 -0400
Message-ID: <>

on tablespace and extent size for RBSOkay, last time I really measured this stuff is a long time ago. The experimental method was times from concurrent manager jobs over business cycles that may have differred somewhat, so the results may have been a little fuzzy, but I think they were good enough to use (and basically free to collect). This all assumes that you've got a reason not to use automatic UNDO (such as being on 8i, in your case. There may well be other reasons not to use automatic UNDO, but I think that would be a different thread.)

If you've got the space, make all the rollback segments the same size, and big enough such that your biggest job (if you could run it in isolation) easily fits without extention. Then back figure the extent size by dividing the total by a number in the range of 20 to 40. That will almost certainly place you in the plateau where various collisions are minimized to about the same degree. Then slap them out there with the number you choose as the initial number of extents. Dynamic growth and shrink are cool, but I use them more as a tool to observe something unusual happened than to conserve space.

Now, remember that SET TRANS... does not reserve the segment, so all manner of transactions may mix in there with your designated big transaction. One way you can attempt reservation is to bring one of the configured segments on line, and immediately place a number of very tiny updates on it using SET TRANS.... where the number is higher than the number of transactions you normally see divided by the number of online rollback segments. This is not bullet proof, but short of setting up an entire instance to run the single job, I'm not aware of a way to do it better (my eyes and ears are open). Of course you have to count the space you lost for the little guys in the original size, and this methodology does not handle running two or more big jobs to the same "large" (remember, I believe you should translate "large" into all the same size, but you're trying to reserve this one) segment. When the transaction completes, you whack the little guys and take the specially named segment off line the way that waits (in case something snuck in there.) Then you check whether your segment grew, etc.,etc., to see if you need to make new plans.

Okay, recapping the short answer -- biggest you need divided by a number in the range 20 to 40 probably will put you in a sweet spot. (And this is totally separate from figuring out how many segments you need.)

  -----Original Message-----
[]On Behalf Of NGUYEN Philippe (Cetelem)
  Sent: Friday, June 18, 2004 2:57 AM
  To: ''
  Subject: RE: on tablespace and extent size for RBS

  sorry for the lack of informations, my question is based on a 8i instance.   I use different uniform-size extents for my LMT depend on the size of the tables but, for rollback segment, do you also use different sizes as we never know (unless making SET TRANSACTION USE RBS....) which rollback and which king of tables we be in use ?

    -----Message d'origine-----
    De :
[]De la part de Goulet, Dick

    Envoyé : jeudi 17 juin 2004 18:15
    À :
    Objet : RE: on tablespace and extent size for RBS

    That would depend on the version of Oracle. Not everyone is running 9i or better.


        I use Locally managed, uniform extents. Extent size depends on the database and it's usage. One has 16K extents in a 1M tablespace where as another has 10M in a 4GB tablespace.

    Dick Goulet
    Senior Oracle DBA
    Oracle Certified 8i DBA

      -----Original Message-----
      From: Justin Cave []
      Sent: Thursday, June 17, 2004 11:49 AM
      Subject: RE: on tablespace and extent size for RBS

      Can you use an UNDO tablespace instead of rollback segments?

      Justin Cave
      Distributed Database Consulting, Inc.


[] On Behalf Of NGUYEN Philippe
      Sent: Thursday, June 17, 2004 9:45 AM
      Subject: on tablespace and extent size for RBS

      Hi List!
      just a little advice on tbs for Rollback Segment :
      do you use DMT, LMT, with uniform  and extent size ?

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Jun 18 2004 - 07:36:46 CDT

Original text of this message