Re: Buffer busy waits are 10.96% of non-idle waits

Read the below carefuly.
Do not forget that any ratio of an event to idle events is not considerable. You must consider it if you see the wait in the top 5 wait event list in statspack report.
If the time waited for buffer waits is in a considerable rate acoording to other waits then you must tune it.

Here is a detailed explanation of buffer busy waits.

This event is caused by:
? multiple sessions requesting the same block (i.e., one or more sessions are waiting for a process to read the requested block into the buffer cache) ? multiple sessions waiting for a change to complete for the same block (only one process at a time can write to the block, so other processes have to wait for that buffer to become available)

If buffer busy waits is high, determine which blocks are being accessed concurrently and if the blocks are being read or changed through V$SESSION_WAIT and V$WAITSTAT.

V$SESSION_WAIT will show the file#, block# and id (where id represents the status of the buffer busy wait event).

? file# - data file number containing the block being read
? block# - block number being waited on
? id - buffer busy wait event:
? 1013/1014 - block is being read by another session
? 1012/1016 - block is being modified

V$WAITSTAT will show the block classes and the number of times waited for each. Different actions may be taken for each block class to alleviate contention. Tuning priorities should be oriented toward the classes that contribute the highest wait time percentage.

segment header waits
Each segment has one segment header block. There are basically two types of segments -- data and index. The following is a brief discussion on causes for segment header blocks based on the data structures they contain: ? Problem: A high insert rate on a table with insufficient transaction free lists results in a bottleneck.
? Solution: Increase free list groups. For databases running in exclusive mode, this recommendation may also circumvent the issue of a small block size constraining the number of available free lists. ? Problem: Under heavy insert activity, a table's High Water Mark (HWM) is constantly updated. This may be due to running out of blocks on the free lists and need to replenish it by allocating new blocks. The default value for incrementing the HWM is 5, which may be insufficient on a busy system or for the average insert size.
? Solution: This value can be increased up to 255 through the undocumented init.ora parameter, _BUMP_HIGHWATER_MARK_COUNT. Caution: this parameter determines how many blocks to allocate per free list when bumping up the HWM. Therefore, this can grow a table very quickly if it has a high number of free lists. For example, if there are 100 free lists and _bump_highwater_mark_count=100, then this may quickly add up to 10000 free blocks to the segment.
? Problem: Constantly inserting new entries into the extent map within the segment header because extent sizes are too small. ? Solution: Increase the size of each extent. Although ORACLE7 release 7.3 allows an object to have unlimited extents, it is better to have a small number of very large extents than to have a large number of small extents.

 Data block waits
 The data block class is used to store data (index or table data). Here are some reasons for data block waits:
? Problem: multiple sessions could be requesting the same block from disk (this could actually happen for each block class). Only one session will do the read from disk, and the other sessions will be waiting for the block to be placed into the buffer cache. The other sessions will be waiting on the buffer busy wait event (1014).
? Solution: the buffer cache may be too small to keep the current working set in memory. Enlarging the buffer cache (db_block_buffers) can help. Another option is to use buffer pools to reduce the number of buffers an object can occupy in the buffer cache. For example, we may effectively limit the number of buffers that a randomly accessed large table can occupy in the buffer cache by placing it in the recycle pool. ? Problem: multiple sessions are going after rows in the same block because it contains so many rows.
? Solution: reduce the number of rows per block (i.e., modify pctfree/pctused settings). This is a space for time tradeoff. The table will use more space, but 'buffer busy waits' will be reduced. ? Problem: multiple sessions are trying to insert into the same block because there is only one free list (or insufficient free lists). ? Solution: adding multiple free lists to the object will increase the number of heads of free lists, thus the contention point can be distributed over the free lists, reducing the number of buffer busy waits.

Free list block waits
This statistic measures contention for "free list group" blocks. Some documentation and tuning scripts claim that waits on this block class indicate that the number of free lists need to be increased for some objects. Most databases that run in exclusive mode see zero waits on this block class because their DBAs do not create objects with free list groups. Otherwise, the reasons and solutions for free list block waits are similar to those of segment header waits. See that section for details.

Identifying block waits by file
X$KCBFWAIT shows a count of buffer busy waits per file. The indx column represents the file id number - 1. So this view can be queried to determine which file has a high number of buffer busy waits. select indx+1 fileno, count, time
  from x$kcbfwait
where time != 0 or count > 0
order by time;

If the file with highest wait time is known, find the objects that belong to that file:
select file_id, segment_name, segment_type, freelists, freelist_groups, pctfree, pctused
from dba_extents
where file_id = <fileno>;

Bunyamin Karadeniz
Oracle DBA
Havelsan A.S. Eskisehir yolu 7.km
Ankara / Turkey
Tel : +90 535 3357729

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, July 11, 2002 8:48 AM

> Guys,
>
> " Buffer busy waits are 10.96% of non-idle waits "
>
> TOAD gives this alarm often. what does it mean ? which view will
> give me the wait statistics ?
>
> " it occurs when a session cannot access a block because it is in
> use by another session. The two most common causes are
> insufficient free lists for a table or insufficient rollback
> segments. " --- IS THIS THE REASON ?
>
> what should be the value of this , i mean , the safe limits ?
>
> How do i approach and solve this issue ?
> kindly enlighten me.
>
> TIA.
> _________________________________________________________
> There is always a better job for you at Monsterindia.com.
> Go now http://monsterindia.rediff.com/jobs
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: oraora oraora
> INET: oraoraora_at_rediffmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bunyamin Karadeniz
  INET: bunyamink_at_havelsan.com.tr

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).