Buffer Cache I/O-Related Wait Events

•  This wait shows up in database writer. DBW waits on "db file parallel write" when waiting for a parallel write to files and blocks to complete. The wait lasts until all submitted IOs are complete.

Individual Waits:

  Parameters:

• P1 = files
• P2 = blocks
• P3 = requests / timeout

• P2 (blocks)

Increase DBWR throughput

DBWR throughput is very platform and version specific so only general observations can be made here. The following items may influence the rate at which DBWR can clear blocks from the cache:

Physical disk attributes (stripe size, speed, layout etc..)

Raw devices versus File System Files

Spreading written data across more disks/files

Using Asynchronous writes where available

Using multiple database writers where asynch. IO is not available. DB_WRITERS in Oracle7, DBWR_IO_SLAVES in Oracle8/9.

Using multiple DB Writer gatherer processes in Oracle8 DB_WRITER_PROCESSES

Setting _DB_BLOCK_WRITE_BATCH to a large number. This parameter is obsoleted in 8.1. 

Using the "Multiple buffer pools" feature in Oracle8 and Higher. See Note:135223.1

Note that there many port specific issues which affect the optimal setup for DBWR on a given platform. These range from choosing a DB_BLOCK_SIZE which is a multiple of the page size used by the operating system for IO operations to configuring Asynchronous IO correctly.

Buffer Cache Size and Configuration

Other things that affect the performance of the buffer cache include:

• DB_BLOCK_BUFFERS is the actual size of the buffer cache itself. Be careful when changing the size of the buffer cache as it affects memory requirements and may also affect whether a table is classed as a small table or a large table for caching during full table scans (small tables are placed at the MRU end of the LRU). 
• In 9i, the parameter DB_CACHE_SIZE specifies the size of the DEFAULT buffer pool for buffers with the primary block size. 9i allows to configure buffer caches of different block size.  DB_nK_CACHE_SIZE specifies the size of the cache for the nK buffers. The value of nk should be other than DB_BLOCK_SIZE.  
• DB_BLOCK_LRU_LATCHES allows multiple LRU chains in the buffer cache from Oracle 7.3 onwards. This generally defaults to a sensible value but can be set explicitly in the init.ora file. On Oracle7 DB_BLOCK_LRU_LATCHES should be set to 2 * number of CPU's. On Oracle8 set it to 2 * number of CPU's * number of Buffer Pools configured.
• In Oracle8 it is possible to have up to 3 separate buffer caches. This is described in the standard documentation and is configured at object level using the BUFFER_POOL storage attribute.
• DB_BLOCK_LRU_STATISTICS should not be set to TRUE
• In Oracle9 this parameter cannot be manually set anymore (not even using _DB_BLOCK_LRU_STATISTICS). It is now hard coded to be 1/2 CPU_COUNT for each buffer cache (DEFAULT, KEEP, RECYCLE, and nK caches).

Most common  Buffer Cache Waits and Latches

Latches:

    Please refers to Note:22908.1 for a complete discussion on detecting and resolving latch contention.

• Cache buffer chain latch:

This latch is acquired  when searching for data blocks cached in the SGA. Since the Buffer cache is a chain of blocks, each of this chains is protected by a child of this latch when needs to be scanned. Contention in this latch can be caused by very heavy access to a single block. This would require the application to be reviewed. As of Oracle8i there are many hash buckets to each latch and so there will be lots of buffers under each latch. 
 
• Cache buffers LRU chain latch:

Processes need to get this latch when they need to move buffers based on the LRU block replacement policy in the buffer cache. Contention in this latch can be avoided implementing multiple buffer pools or increasing the number of LRU latches with the parameter DB_BLOCK_LRU_LATCHES (The default value is generally sufficient for most systems). SQL tuning can affect this as well by reducing data blocks visited by a query.

The behavior of this latch can be affected when extended statistics are enabled using the parameters DB_BLOCK_LRU_EXTENDED_STATISTICS and DB_BLOCK_LRU_STATISTICS (These parameters were removed in Oracle8i)

• Buffer busy wait:

This event is commonly caused when multiple session are trying to read the same block or multiple session waiting for a change to complete in the same block. Block contention corrective actions depends on the type of block involved. Query on V$WAITSTAT and X$KCBFWAIT to detect the hottest blocks breaking down by the type of block.  To reduce buffer busy waits on: 
 
data blocks:
• Reduce number of rows per block whether changing pctfree/pctused or reducing the DB_BLOCK_SIZE.
• Check for 'right-hand-indexes' (indexes that get inserted into at the same point by many processes). You can use reverse key indexes to distribute the different information.

See Note:155971.1 for a detailed case-study on how to diagnose and resolve intensive random access performance problems. 

segment header:
• Use freelists or increase of number of freelists.
• Extent size too small can cause contention on the header when the table grows regularly. Consider increasing the extent size for the table.


        undo header: 
 

• Add more rollback segments to reduce the number of transaction per rollback segment.
• Reduce the value of the parameter TRANSACTIONS_PER_ROLLBACK_SEGMENT


        undo block: 
 

• Consider making rollback segments larger in exclusive mode

 
• Free buffer wait:

This will mostly occur because of DBWR not writing out buffers fast enough. Please refers to the section "Increase DBWR throughput" to improve the speed of this process.