Re: Rollback segments question (shrink)

Jonathan Lewis wrote:
>
> 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)
> >
> >
> >
> >
> >
> >

On a similar tack, I've had success by letting the tablespace files autoextend up to a known size and setting the rollback segs maxext unlimited.

Then each night before backup, shrink or drop them, resize the file down and then backup.

Connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"