| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Rollback segments question (shrink)
can some one really make it clear to me - from what I know that if a transaction is running and nothing is commited and it continues to go to another extent, the previous extent will not shrink until the transaction has commited - does n't that make sence. It cannot get rid of the data that is not commited yet. It will continue to grow over other extents until it commits without shrinking and destroying the before images.
Also I dont understand the purpose of optimal size - so fine we shrink to the optimal size if it grows above that level. But you cannot shrink the RBS if it is fully occupied by the same transaction still running and not commited.
please correct me if I am wrong.
Naushi
Connor McDonald wrote:
> 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)
> > >
> > >
> > >
> > >
> > >
> > >
>
>
>
 |
 |