Re: Rollback segments question (shrink)

A rollback segment will shrink when an active transaction fills one extent and is about to start using a second extent. At this point Oracle will check the size of the rollback segment against the optimal size, and check where in the rollback segment the oldest active transaction starts.

If possible, Oracle will then drop as many extents as possible to get as close as possible to the optimal size without impacting on the oldest active transaction.

In your example, R01 can only shrink when the current extent (10Mb) becomes full and a transaction wants to cross to a new extent.

There are some good strategies for 'well-behaved' systems, but if you have completely random, and uncontrollable occurrences of extreme transactions there is no really great answer to your problem.

One thought is to reduce your extent size - I tend to suggest that the OPTIMAL size should be equivalent to 10 to 20 extents. If you did this, your extreme transactions would go to a very large number of extents, but at least the shrink would occur much more quickly, as other transactions would have to fill (say) 1MB of the overextended rollback segment to trigger a shrink, rather than 10MB.

Personally I would not use MAXEXTENTS, or tablespace unlimited, as this leaves you the option for a complete runaway (or bug) causing a catastrophe. If space was an issue I would aim to set the tablespace size to:

    size of one rollback at worst case size + 2 x number of rollbacks at double their optimal.

if not, I would set the tablespace to be

    number of rollback segments x size of worst case. Empty space causes no harm (other than wasted backup time).

In the space limited case, I would also try to work out the average time between extreme transactions and run a dbms_job at half that time interval to force a shrink to optimal of any rollback segment that was more than one extent over optimal.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Syltrem wrote in message <14476.41867$903.318784_at_weber.videotron.net>...


>Hi!

>

>Let's say I have a tablespace total size 100M.

>

>Inside it 2 rollback segments, R01 and R02 with INITIAL=NEXT=10M

>MINEXTENTS=2 MAXEXTENTS=UNLIMITED OPTIMAL=20M.

>

>One transaction starts using R01 and allocates 6 extents.

>Transaction finishes. Rollback segment R01 not automatically shrunk by

>Oracle. That leaves 20M free in the tablespace (6 allocated to R01, 2 by

>R02)

>

>Another transaction starts, using R02. It also requires 6 extents. It bombs

>out.

>

>It "looks" like I have enough space in the rollback segment, but the

 problem


>is that it is never de-allocated. I have to manually do a ALTER ROLLBACK

>SEGMENT R01 SHRINK to free it and keep transaction 2 from crashing.

>

>* When are the rollback segments shrunk, by default?

>* Do I have to shrink them manually once in a while?

>* Is it a good idea to have MAXEXTENTS UNLIMITED? I do this because

>sometimes users do huge transactions and this is unpredictable - they use a

>paackage and I can't easily direct big transactions to a specific rollback

>segment.

>* Never mind the numbers if you don't like them, it's just for the example!

>

>Thanks!

>

>Syltrem

>http://pages.infinit.net/syltrem (OpenVMS Web Site)

>

>

>

>

>

>