Re: BUFFER BUSY WAITS

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
> --
Received on Mon Feb 18 2002 - 17:56:02 CST