BUFFER CACHE之三：主要的等待事件

原因：资源紧张，等待其释放。
原因的原因：1. lgwr和DBWn进程写太慢；2. Buffer和latch不可用
原因的原因的原因：全表扫描、library cache latches数太多等。
视图：V$SYSTEM_EVENT (instance-level waits), V$SESSION_EVENT (session-level waits)
注意： 等待与共享池和缓冲池中的高命中率（high hit ratios）无关！换句话说，高命中率也会有长时间的等待。

* Buffer Busy Waits
This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently. To determine the wait statistics for each class of buffer, query the v$waitstat view. Common buffer classes that have buffer busy waits include:

缓冲池中的buffer块被多个进程并发访问，用v$waitstat视图查看各类buffer的等待统计数据。

- Data Block
If the contention is on tables or indexes (not the segment header):
Check for SQL statements using unselective indexes. Check for right-hand-indexes (that is, indexes that are inserted at the same point by many processes; for example, those which use sequence number generators for the key values).

Consider using automatic segment-space management or increasing free lists to avoid multiple processes attempting to insert into the same block.

- Data Block (continued)
The v$session_wait view will provide the file and block numbers (in the
P* columns) for those blocks that have the most frequent block waits. These blocks can then be mapped to which object they belong.

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

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

* Free Buffer Inspected

This is a measure of how many buffers on the LRU list are inspected by a process looking for a free buffer (writing a new block) before triggering DBWn to flush the dirty buffers to disk.

* Free Buffer Waits

This wait event indicates that a server process was unable to find a free buffer and has requested the database writer to make free buffers by writing out dirty buffers. A dirty buffer is a buffer whose contents have been modified. Dirty buffers are freed for reuse when DBWn has written the blocks to disk.

To resolve the contention, DBWn has to make buffers available faster for
overwriting. To achieve this, examine ways of speeding up the write process. This event is also an indication that the buffer cache is too small. Examine the hit ratios for the buffer cache to determine if the cache should be resized. If this event occurs frequently, examine the session waits for DBWn to determine whether there is anything delaying DBWn.

Causes

DBWn may not be keeping up with writing dirty buffers in the following situations:
- The I/O system is slow.
Solution: Check that the files are equally distributed across all devices. If that produces no effect get faster disks or place offending files onto faster disks.

- The I/O is waiting for resources, such as latches.
Solution: Check that the files are equally distributed across all devices. If that produces no effect get faster disks or place offending files onto faster disks.

- The buffer cache is so small that DBWn spends most of its time cleaning out buffers for server processes.
Solution: Increase the buffer cache size.

- The buffer cache is so large that one DBWn process cannot free enough buffers in the cache to satisfy requests.
Solution: Decrease the buffer cache size or initialize more database writer processes.

 

 

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

转载于:http://blog.itpub.net/24463783/viewspace-671914/