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.
> Now for your question, I very often see the rollbacks shrunk to just 3
> extents after a day or two of processing.
So? That doesn't indicate shrinking in the reverse direction of travel.
>This happens when the next
> tran gets assigned to that rollback and all of the 4MB extents are not
> in use. This has happened in 7.3.4, 8.0.4, 8.0.5 and 8.0.6. I've been
> unable to think of a way keep the smaller extents around besides
> recreating the rollback segments once a week (or more often).
>
I'm surprised you haven't seen your segments reduced to 2 extents (since
that would add up to 100M). That doesn't tell us anything about the
direction of travel, however. In fact it rather confirms the usual idea of
forward travel only, since you'd be moving out of a 50M extent, and looking
forward into the 4M extent -and deciding that you can drop it. What's worse
is you're using optimal. Very bad for performance, having those segments
shrink whenever they want to.
Why not get rid of optimal, and schedule an 'alter rollback segment shrink
to 100M' at the dead of night when nobody gives a damn about performance?
On the other hand, there's so much that's, er, unusual about the way you're
using your rollbacks that it probably isn't worth the effort.
Regards
HJR
> Big Al
Received on Wed Oct 04 2000 - 04:09:47 CDT
