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 --
Received on Fri Feb 15 2002 - 14:30:01 CST
