Re: undo header and buffer busy wait

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3C6D8667.598E_at_yahoo.com>...
> Howard J. Rogers wrote:
> >
> > Comment below
> > HJR
> > --
> > ----------------------------------------------
> > Resources for Oracle: http://www.hjrdba.com
> > ===============================
> >
> > "Mark D Powell" <mark.powell_at_eds.com> wrote in message
> > news:178d2795.0202150733.2d82e37_at_posting.google.com...
> > > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:<1013741618.970613_at_bugstomper.ihug.com.au>...
> > > > More rollback segments. Bigger rollback segments. And get rid of
> optimal.
> > > >
> > > > Regards
> > > > HJR
> > > > --
> > > > ----------------------------------------------
> > > > Resources for Oracle: http://www.hjrdba.com
> > > > ===============================
> > > >
> > > > "Mike F" <u518615722_at_spawnkill.ip-mobilphone.net> wrote in message
> > > > news:l.1013722665.1895874023@[64.94.198.252]...
> > > > > We are using 8.1.7.2 on solaris.
> > > > > We have usually 20 sessions, and we have 13 rollback segs, each one
> > > > > with 50k initial, with optimal set to 500k. Even though, we still get
> > > > > a lot of undo header wait and the buffer busy wait is always on
> > > > > rollback tablespace, how should I do to improve it?
> > > > >
> > > > > Our I/O wait from top command shows we have constantly 5 to 10%
> > > > >
> > > > > Thanks
> > > > >
> > >
> > > Mike, I disagree with 2 out the 3 recommendations Howard gave you.
> >
> > Was I having an off day?!
> >
> > > You only have 20 concurrent sessions so the odds are only a couple of
> > > concurrent transactions at any time so you do not need more rbs
> > > segments.
> >
> > True... but even you mentioned 'the odds'. Since the only cost of extra
> > rollbacks is a bit of disk space, it can't do any harm to try... I confess
> > to being a bit doubtful of the way the original poster's maximum of 20 was
> > measured.
> >
> > >But Howard is right that you need bigger segments. Half a
> > > meg is way too small for a rbs segment as your average OLTP will have
> > > numerous transactions that process a couple of megabytes. And do not
> > > forget to consider the size of batch transactions. I would recommend
> > > you consider making your extent size at least 500K and the segment
> > > size 10 to 20 times this.
> >
> > Ye olde myth about 20 extents, huh? 6 will do fine (see posts passim and
> > Steve Adams)
> >
> > >Then I would use the optimal setting; it
> > > promotes the efficient reallocation of rbs space to the segment that
> > > needs it when your segments have to handle a wide mix of transaction
> > > types. The use of optimal will generally not cause any harm as long
> > > as the optimal size is set larger than the average active rbs size.
> >
> > Profoundly disagree... optimal will slow down every transaction that causes
> > a wrap (ie, a move from one extent to another)... it must take time out to
> > check whether the segment is bigger than optimal, and then de-allocate
> > extents accordingly if so.
> >
> > > In fact I want my optimal to be 5 to 10 times this size in an OLTP and
> > > for the average active size to be equal to 1 or 2 extents of the rbs
> > > segments. This gives you a database where you do not have significant
> > > contention for rbs resources.
> >
> > You seem to be setting optimal so high that under normal circumstances (ie,
> > no rogue blocking transactions that induce substantial growth) it's never
> > going to be invoked to cause a shrink. That kinda confirms in a round about
> > sort of way, that automatic shrinking is not something especially desirable.
> > But some of the pain of optimal is also incurred even when a shrink does not
> > take place.
> >
> > Call me an old cynic: but when Oracle ever does anything for you
> > automatically, there's usually a price to pay -and usually in the currency
> > of 'performance'.
> >
> > But each to his own.
> > Regards
> > HJR
> >
> > >
> > > If by chance all 20 connections are always transactions then I would
> > > want to have 20 rbs segments, but that would be a very unusual
> > > situation.
> > >
> > > The statspack scripts produce some very useful information on rbs
> > > segments and are very easy to use. You might want to use it to
> > > monitor the effectiveness of your changes.
> > >
> > > Just another take.
> > >
> > > -- Mark D Powell --
>
> I'm a fan of the ol' 20 extents policy - but for the outdated reason -
> more that if you grow by an extent, you're basically adding 5% to your
> rollback size. If you had (say) 2 extents, then adding an extent is
> adding 50%. I like the small increments with the aim of keeping as much
> as possible in buffer.
>
> hth
> connor

I have read Steve Adams article on the subject and I did not agree with his conclusion. I do not remember the details but I think it had to do with what the goal is of having multiple extents allocated to the rbs segments is. I want to be able to hold onto changed data for as long as I can to support long running jobs like some of our monthly purge and archive jobs which remove around 100M to 300M of data out of 1G - 2G tables every month while the great majority of my activity consists of small transactions.

Consider the following: I have set my rbs extent size to my average active rbs segment size of 5M, and I have allocated 10 extents to every rbs segment setting the optimal at 10 X 5M = 50M. My segments do not shrink very often but they do automatically clean up so I never have to manually shrink them. Per the Oracle rbs tuning queries and Statspack we effectively have no rbs segment contention, and we can count the snapshot too old errors we get per year on one hand. Nor has any (non-DBA) job failed with an unable to extend of a rbs segment in years under this arrangment. Isn't the above the goal?

Note to HJR: You have one of the most effectively organized web sites I have ever visited.

• Mark D Powell --