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.