Re: About rollback consumption

"Howard J. Rogers" wrote:
>
> Comments below.
> HJR
> --
> --------------------------------------------------------------------------
> Opinions expressed are my own, and not those of Oracle Corporation
> Oracle DBA Resources: http://www.geocities.com/howardjr2000
> --------------------------------------------------------------------------
>
> "Big Al" <db-guru_at_att.net> wrote in message
> news:39D92A8C.BACBE0A6_at_att.net...
> > "Howard J. Rogers" wrote:
> > > <<snipped>>
> > > When rollback segments shrink to optimal as they cross the extent
 boundary,
> > > are you saying that they can now drop extents 'behind' the direction of
> > > travel?
> >
> > Yes. I have a very high volume system that I create rollbacks as
> > follows:
> > initial 4m next 4m minextents 25 optimal 100m
> > Then I do an alter on the rollbacks to change the next extent size to
> > 50m. The theory behind this is to create a lot of small (4m is small
> > for this system) rollback extents for the online transactions to use
> > without causing undo header waits. At the same time a batch job that
> > runs though the rollback segment and needs extents gets 50m at a time.
> > This cut down batch time tremendously. Batch jobs were spending a lot
> > of time waiting for rollback extents before this.
> >
>
> This is awful rollback segment management. Don't take it personally, it
> just is. You should have separate rollback segments for batch transactions,
> and either use online-offline tablespace techniques to make sure the batch
> transaction uses the big segments, or use the 'set transaction use rollback
> segment blah' bit of code to direct the right transaction to the right
> segment. The trouble with your solution is that there's nothing to stop the
> online transactions using the large extents, and you've guaranteed
> fragmentation in your rollback segment with its odd-sized extents.

I agree, except for one major factor. The system is an SAP system and we don't control the code. We can't send transactions and most of the batch to specific rollback segments. Plus batch tends to run around the clock at month end and sometimes in between. With all small extent sizes batch runs forever and with fewer rollback extents I see a lot of header waits. I have considered having many huge rollback extents (say 5 of 50MB and optimal 250MB) to reduce the management nightmare I have. That's not much more storage considering we have about a terabyte right now.

Big Al Received on Tue Oct 03 2000 - 16:24:37 CDT