Re: BUFFER BUSY WAITS

Ricky Sanchez wrote:
>
> Paul-
>
> 1. Take the Rich Niemic book and toss it in the trash. The man is a
> complete idiot.
>
> 2. Get a Statspack report for the busy period. If "buffer busy waits"
> are not in the top five wait events at the beginning of the report, you
> do not have a buffer busy waits problem. You have some other bottleneck,
> as evidenced by the #1 wait event. If you have not installed Statspack,
> do so immediately. You can find the scripts on $ORACLE_HOME/rdbms/admin.
> Takes about five minutes. Works great. Read the doc.
>
> 3. Buffer busy waits may have a number of root causes. Get a statspack
> report for the busy period and look at the "buffer busy" section. See if
> you have Data, Segment Header, Undo or Undo Header waits. If the main
> wait is data, you have to further analyze them and see if they are table
> or index waits. The treatment of each is quite different. Some sql is at
> the end of this message to help out. Play with it, it is coming off the
> top of my head and I am syntax-error prone.
>
> Diagnostic Notes:
>
> Undo / Undo header waits: generally, increase the number of rollback
> segments to spread the transaction load among more rollback segments,
> which headers are also known as "transaction table".
>
> Segment header: you might need to implement Freelist Groups. This
> creates additional header blocks for the table to store freelist
> information. Freelist groups were initially meant for parallel server,
> but work fine for exclusive instances as well. Requires recreating the
> table.
>
> Data/Table: Probably means you need to increase freelists for the table,
> or perhaps implement freelist groups as mentioned above. This is
> probably the case if the sql operation is an insert. If the sql op is an
> update, you might have a silly application design problem, with many
> users updating the same rows at the same time. In that case, fix the
> application.
>
> Data/Index: Poor application design is probably the issue. Most likely
> heavy inserts with a monotonic sequentially increasing index, as when a
> sequence is used. Key values get skewed, since each new value must go
> into the same block as the previous key. Index key values are physically
> ordered, so there are two ways around this: 1) use something other than
> a sequence to assign key values or 2) use a "reverse key" index. Reverse
> key indexes do a byte reversal on key values before insertion, so each
> new sequence number get stuffed into a different block than the previous
> value. You might also have too many indexes. If so, analyze the
> application and get rid of some indexes.
>
> Well, that is the short course on buffer busy waits. More to tell, but
> no space or time now. Check Metalink for better info. Maybe Jonathan
> will jump in here with the obvious stuff I missed.
>
> And get rid of that Rich Niemic crap.
>
> - ricky
>
> SQL to diagnose buffer busy waits:
>
> to find the hot block, run repeatedly during busy period. Spool results,
> look at top "p1" and "p2" value pairs:
>
> select event, p1, p2 from v$session_wait where event = 'buffer busy';
>
> p1 is the file#, p2 is the block. Plug values into this query:
>
> select segment_name, segment_type, owner, tablespace_name
> from sys.dba_extents
> where file_id = <p1>
> and <p2> between block_id and (block_id+(blocks-1));
>
> The idea is to find what buffer is busiest, then figure out what object
> is involved. Read above notes to figure out how to respond to each
> object type.
>
> "Paul L." wrote:
> >
> > Can anyone tell me how to get rid of Buffer Busy Waits ? I am getting them
> > on my datafiles for indexes, and some tables.
> > I tried to increase the initrans parameter as suggested by Rich Niemic in
> > his tuning book, but it didn't help.
> >
> > Does any one have any experience with this ?
> >
> > Thanks.
> > --
> > Paul
> >
> > --
> > Paul S. LaBarbera
> > University of Phoenix Online Faculty
> > paul821_at_email.uophx.edu
> > --

I did not rate the book highly - but the man *did* sit down and put pen to paper which was more than most of us (including myself) have done. So I don't think its appropriate to launch a tirade upon him.

hth
connor

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

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."