Re: About rollback consumption

Hi Al,

When you don't control the source code, that's time to start practising your 'alter tablespace X offline' technique! If the batch process can't see the little segments, it can't use them.

Plus, remember that once a transaction has *started* in a rollback segment, it must *finish* in that segment. In other words, though your batch transaction runs for ages, you only need to have off-lined the little ones for a minute or two... once it's underway, you can online them again.

Regards
HJR "Big Al" <db-guru_at_att.net> wrote in message news:39DA4E27.3C908B20_at_att.net...
> "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 - 18:59:17 CDT