Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: undo header and buffer busy wait

Re: undo header and buffer busy wait

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Fri, 15 Feb 2002 22:06:31 +0000
Message-ID: <3C6D8667.598E@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

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."
Received on Fri Feb 15 2002 - 16:06:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US