Re: Rollback segment/tablespace growth problem

Sybrand Bakker wrote:
>
> "Mr. Chow Wing Siu" <wschow_at_Comp.HKBU.Edu.HK> wrote in message
> news:9p5l2j$h2c$1_at_net44p.hkbu.edu.hk...
> > Lawrence Simela <lsimela_at_mahalini.prestel.co.uk> wrote:
> > > Issue
> > > ALTER ROLLBACK SEGMENT rollback_seg_name SHRINK TO new_size;
> > > If you do not specify TO new_sized then it will be shrunk to OPTIMAL
> size or
> > > MINEXTENTS if OPTIMAL was not specified in storage clause
> > > hth
> > > lawrence
> >
> > Hi,
> >
> > I did it before seeing your post. It works. My next questions
> > are: how to determine the optimal size, why rbs cannot shrink
> > by itself and need to be done manually, if optimal size is set,
> > what's the sql "alter rollback segment r01 shrink"?
> >
> > Why designer2000 needs higher extension and causes rbs full recently?
> >
> > Thanks.
> >
> > --
> > Johnson Chow
>
> 1 a guideline for optimal is setting it to 2 times minextents times the
> size of an extent
> 2 if you set optimal Oracle *will* shrink the rollback segment
> automatically.

Oracle does not shrink a rollback segment until it needs it again. That is, a rollback will remain at the size it was when last used until it is used again, then Oracle will shrink it back to optimal. It does this so that the rollback information will be available in case another transaction needs the information to provide read consistency.

> You may not want that because continuous space allocation is -before 8i-
> very expensive.
> 3 alter rollback segment <segment_name> shrink
> is the sql to shrink manually.

The only time I do this is if I have a very large transaction to run (a "special") and need all of the rollback space I can get without extending the tablespace. In this case I will shrink all of the rollback segments back to one extent -- they are usually at optimal of 10 to 20 extents (depending upon the db).

> 4 Designer 2000 is a complicated system and many tables and indices are
> typically involved in one single transaction.
> 150M is definitely not big for a rollback segment.

If you mean for optimal, I agree. If you mean extent size in a heavy batch environment or data warehouse, possibly true. For extent size a very heavy OLTP environment, probably not true. For OLTP a good place to start, IMCO and experience 10 to 12 meg extents with 10 to 20 extents minimum and a corresponding optimal.

>
> Hth,
>
> Sybrand Bakker, Senior Oracle DBA

-- 
Andrew Allen
Livonia, MI
E- Mailto:ajalle_at_ameritech.net