Buffer Cache I/O-Related Wait Events

These Wait Events occur because of Buffer Cache operations involving the DBWR process(es) and I/O Slaves.

'db file parallel write' , 'db file single write',  'write complete waits',  'free buffer waits'

•  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

• files Number of files

  This indicates the number of files that Oracle is writing to.

• blocks Number of blocks

  This indicates the total number of blocks to be written.

• requests / timeout Num requests / timeout

  From Oracle9.2 onwards P3 shows the timeout value in centiseconds to wait for the IO completion.

  Prior to Oracle9.2 this indicates the total number of I/O requests, which will be the same as P2 (blocks)

NOTE: From 9.2.0.5 to 10.2, the definitions have changed: P1 = requests  

This indicates the total number of I/O requests, which will be the same as blocks

P2 = interrupt P3 = timeout  

This indicates the timeout value in centiseconds to wait for the IO completion.

  Wait Time:

The wait lasts until ALL of the submitted IO requests are complete. There is no Oracle timeout on this wait prior to Oracle9.2 . From 9.2 onwards the timeout is indicated by   P3

  Finding Blockers:

This wait should only occur in database writer processes. The blocker is the Operating System IO subsystem.

Systemwide Waits:

If this is a significant portion of the total wait time it is not necessarily having a large impact on user sessions. If user sessions show a large wait time on "write complete waits" and / or "free buffer waits" then this is impacting user sessions. A less obvious impact can be on the IO subsystem in that the writes may be impacting read times of sessions reading from the same disks. One can look at:

• <> to see where writes are occuring (although the write TIMES in this view are often wrong).
• Operating system IO subsystem monitoring tools to see write performance
• <> to see if the target recovery time is leading to excessing writing of blocks from the cache.

Reducing Waits / Wait times:

Most options which can affect DBW throughput are documented in   Note:62172.1. 

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)

Wait events:

• 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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29337971/viewspace-1816626/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29337971/viewspace-1816626/