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

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

Re: undo header and buffer busy wait

From: Mark D Powell <mark.powell_at_eds.com>
Date: 15 Feb 2002 07:33:27 -0800
Message-ID: <178d2795.0202150733.2d82e37@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. 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. 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. 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. 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.

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.

Received on Fri Feb 15 2002 - 09:33:27 CST

Original text of this message

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