Oracle Instance Tuning (Buffer wait classes)

Oracle Instance Tuning (Buffer wait classes)

Written by: Alireza Kamrani


Buffer busy waits

Possible Cause:
This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently.

Depends on buffer type. For example, waits for an index block may be caused by a primary key that is based on an ascending sequence.

General Area: Buffer cache, DBWR


Examine V$SESSION while the problem is occurring to determine the type of block in contention.

Query V$WAITSTAT for the wait statistics for each class of buffer.

Common buffer classes that have buffer busy waits include data block, segment header, undo header, and undo block.

Check the following V$SESSION_WAIT parameter columns:

• P1: File ID
• P2: Block ID
• P3: Class ID

Causes

To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for buffer busy waits.

For example:

SELECT row_wait_obj#
  FROM V$SESSION
WHERE EVENT = 'buffer busy waits';

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION.

For example:

SELECT owner, object_name, subobject_name, object_type FROM DBA_OBJECTS WHERE data_object_id = &row_wait_obj;

Actions
The action required depends on the class of block contended for and the actual segment.

Segment Header
If the contention is on the segment header, then this is most likely free list contention.
Automatic segment-space management(ASSM) in locally managed tablespaces eliminates the need to specify the PCTUSED, FREELISTS, and FREELIST GROUPS parameters.

If possible, switch from manual space management to automatic segment-space management (ASSM).

The following information is relevant if you are unable to use ASSM (for example, because the tablespace uses dictionary space management).

A free list is a list of free data blocks that usually includes blocks existing in several different extents within the segment.

Free lists are composed of blocks in which free space has not yet reached PCTFREE or used space has shrunk below PCTUSED. Specify the number of process free lists with the FREELISTS parameter. The default value of FREELISTS is one. The maximum value depends on the data block size.

To find the current setting for free lists for that segment, run the following:

SELECT SEGMENT_NAME, FREELISTS
  FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = segment name
   AND SEGMENT_TYPE = segment type;


Set free lists, or increase the number of free lists. If adding more free lists does not alleviate the problem, then use free list groups (even in single instance this can make a difference).

If using Oracle RAC, then ensure that each instance has its own free list group(s).

Data Block
If the contention is on tables or indexes (not the segment header):

• Check for right-hand indexes. These are indexes that are inserted into at the same point by many processes. For example, those that use sequence number generators for the key values.
• Consider using ASSM, global hash partitioned indexes, or increasing free lists to avoid multiple processes attempting to insert into the same block.

Undo Header
For contention on rollback segment header:
• If you are not using automatic undo management, then add more rollback segments.

Undo Block
For contention on rollback segment block:
• If you are not using automatic undo management, then consider making rollback segment sizes larger.

db file scattered read
This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations.

A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan.
The db file scattered read wait event identifies that a full scan is occurring.

When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other.
Such reads are called scattered read calls, because the blocks are scattered throughout memory.

This is why the corresponding wait event is called 'db file scattered read'.

multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for 'db file scattered read'.

Check the following V$SESSION_WAIT
 parameter columns:

• P1: The absolute file number
• P2: The block being read
• P3: The number of blocks (should be greater than 1)

Actions
On a healthy system, physical read waits should be the biggest waits after the idle waits.

However, also consider whether there are direct read waits (signifying full table scans with parallel query) or db file scattered read waits on an operational (OLTP) system that should be doing small indexed accesses.
Other things that could indicate excessive I/O load on the system include the following:
• Poor buffer cache hit ratio
• These wait events accruing most of the wait time for a user experiencing poor response time

Managing Excessive I/O
There are several ways to handle excessive   I/O waits.
In the order of effectiveness, these are as follows:
• Reduce the I/O activity by SQL tuning.
• Reduce the need to do I/O by managing the workload.
• Gather system statistics with DBMS_STATS package, allowing the query optimizer to accurately cost possible access paths that use full scans.
• Use Automatic Storage Management.
• Add more disks to reduce the number of  I/Os for each disk.
• Alleviate I/O hot spots by redistributing    I/O across existing disks.

The first course of action should be to find opportunities to reduce I/O.
Examine the SQL statements being run by sessions waiting for these events and statements causing high physical I/Os from V$SQLAREA.

Factors that can adversely affect the execution plans causing excessive I/O include the following:
• Improperly optimized SQL
• Missing indexes
• High degree of parallelism for the table (skewing the optimizer toward scans)
• Lack of accurate statistics for the optimizer
• Setting the value for DB_FILE_MULTIBLOCK_READ_COUNT  initialization parameter too high which favors full scans

Inadequate I/O Distribution
Besides reducing I/O, also examine the I/O distribution of files across the disks.
Is I/O distributed uniformly across the disks, or are there hot spots on some disks?
Are the number of disks sufficient to meet the I/O needs of the database?
See the total I/O operations (reads and writes) by the database, and compare those with the number of disks used. Remember to include the I/O activity of LGWR and ARCH processes.

Finding the SQL Statement executed by Sessions Waiting for I/O

Use the following query to determine, at a point in time, which sessions are waiting for  I/O:

SELECT SQL_ADDRESS, SQL_HASH_VALUE
  FROM V$SESSION
WHERE EVENT LIKE 'db file%read'; 

Finding the Object Requiring I/O
To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for db file scattered read.

For example:

SELECT row_wait_obj# FROM V$SESSION WHERE EVENT = 'db file scattered read';

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION.

For example:

SELECT owner, object_name, subobject_name, object_type FROM DBA_OBJECTS WHERE data_object_id = &row_wait_obj;

db file sequential read
This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return.

A sequential read is a single-block read.
Single block I/Os are usually the result of using indexes.

Rarely, full table scan calls could get truncated to a single block call because of extent boundaries, or buffers present in the buffer cache. These waits would also show up as db file sequential read.

Check the following V$SESSION_WAIT
parameter columns:
• P1: The absolute file number
• P2: The block being read
• P3: The number of blocks (should be 1)

See Also:
"db file scattered read" for information about managing excessive I/O, inadequate I/O distribution, and finding the SQL causing the I/O and the segment the I/O is performed on.

Actions
On a healthy system, physical read waits should be the biggest waits after the idle waits.
However, also consider whether there are db file sequential reads on a large data warehouse that should be seeing mostly full table scans with parallel query.

The following figure shows differences between these wait events:
• db file sequential read (single block read into one SGA buffer)
• db file scattered read (multiblock read into many discontinuous SGA buffers)
• direct read (single or multiblock read into the PGA, bypassing the SGA)