Re: Rollback segment/tablespace growth problem

From: Howard J. Rogers <howardjr_at_www.com>
Date: Sun, 30 Sep 2001 05:18:19 +1000
Message-ID: <3bb61dfa@news.iprimus.com.au>

"Andrew Allen" <ajallen_at_mailhost.det.ameritech.net> wrote in message news:3BB5C681.F8642BD9_at_mailhost.det.ameritech.net...
> 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.

>

A bit vague. To be more precise, a rollback segment will shrink when a new transaction tries to cross an extent boundary (i.e., write the next bit of its rollback into another extent). The reason it does that is because it is only when determining in which new extent to place the rollback that it gets the opportunity to check whether extents can be discarded.

> > 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).
>

I'd love to know how you pull off that particular trick, since MINEXTENTS for a rollback defaults to 2, and cannot be anything less than that because of the very nature of the way they work.

There is a 'feature' of 8i whereby rolback segments created in locally managed tablespace appear (in dba_rollback_segs) to have a MINEXTENTS of 1, but that's not true, as a quick check of dba_extents immediately after creation with a simple 'create rollback segment blah tablespace rbslocal;' command will show.

> > 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.
>

There is zero reason for any rollback segment to have more than half a dozen extents, so long as they are properly sized. The very last thing to do in an OLTP environment, if you care about performance, is to set optimal -because transactions take time out to provoke extent de-allocation (though that's less of a worry with locally managed tablespace than it used to be, granted).

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

>
>

> --
> Andrew Allen
> Livonia, MI
> E- Mailto:ajalle_at_ameritech.net
Received on Sat Sep 29 2001 - 14:18:19 CDT