Re: [Q] Rollback segments; Small vs. large extents.

I set the optimal parameter and tune it instead. Thus, if my initial and next extent is 1M on all rollback segments, I get 505 extents (on 8K block size) leading to 505M rollback space for a query and that is usually enough.

See the guide on tuning the optimal parameter. Virtually all snapshot too old errors have disappeared at our shop after tuning the OPTIMAL parameter and using that instead of several individual rollback segments.

In article <32c2b3ef.176977810_at_news.sedona.net>,

   102621.3152_at_compuserve.com (Doug Surplus) wrote:
>On Fri, 20 Dec 1996 09:22:27 +0100, Dana Stockler
><stockler_at_ingdata.no> wrote:
>
>>Seasons greeting to all,
>>
>>I have been having some problems with the rollback segments
 on
>>the database we use for software development. The
 transactions
>>here vary in size from 'petit' to 'humongous'.
>>
>>I have implemented what I consider to be a good mix of small
 and
>>large segments. The small segments have an extent size of
 100K
>>and the large segments have an extent size of 1M.
>>
>>I have been tempted to increase the extent sizes to 1M and
 5M,
>>but I'm not sure of the tradeoffs involved and I don't want
 to
>>find out the hard way.
>>
>>I have read a number of books and articles, and the subject
 of
>>optimal rollback segment extent size is still rather murky
 for me.
>>
>>Question 1.
>>What are the advantages of using small rollback segment
 extents?
>>
>>Question 2.
>>What are the advantages of using large rollback segment
 extents?
>>
>>Question 3.
>>Is there any reason why I shouldn't just create all rollback
>>segments with the same large (say 5M) extent size?
>>
>>Thank you in advance for any and all input.
>>
>>Regards,
>>Dana
>
>You can also assign large transactions to specific rollback
 segments
>with 'SET TRANSACTION USE ROLLBACK SEGMENT
 rollback_segment_name' as
>the first statement in a transaction. Read only transactions
 can be
>set no using a rollback segment at all with 'SET TRANSACTION
 READ
>ONLY' .
>
>See the SQL Language Reference Manual for details.
>
>Sometimes SET TRANSACTION isn't feasible or practical so
 another
>tactic I have used when running large imports or other huge
>transactions is to offline all rollback segments except one
 with
>extents sized large enough to handle the transaction.
 Unfortunately,
>rollback segment management isn't yet dynamic enough to
 preclude
>active management of the segments.
>

--
My opinions are strictly mine and not those of my employer.

Badri Narasimhan
DBA
AGCO Corporation
badri_at_concentric.net