The main wait event for BUFFER cache

Reason: Resources are tight and waiting for their release.

Cause of the reason: 1. LGWR and DBWN process write too slow; 2. Buffer and latch not available

Reasons for the Cause: Full table scan, library cache latches number too many.

Views: V$system_event (Instance-level waits), V$session_event (Session-level waits)

Note: Waiting is not related to high hit ratios in the shared pool and buffer pool! In other words, high hit ratios also have a long waiting time.

* Buffer Busy Waits

This wait indicates that there is some buffers in the buffer caches that multiple

Processes is attempting to access concurrently. To determine the wait statistics for

Each class of buffer, query the v$waitstat view. Common buffer classes that has

Buffer busy waits include:

Buffer blocks in the pool are accessed concurrently by multiple processes, and the V$waitstat view is used to view the waiting statistics for each 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's, indexes that's 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 would provide the file and block numbers (in the

p* columns) For those blocks, that has the most frequent block waits. These

Blocks can then is mapped to which object they belong.

-Undo Header

Displays contention on rollback segment Header: If is not using automatic

Undo management, then add more rollback segments.

-Undo Block

Displays contention on rollback segment block:if is not using automatic

Undo management, consider making rollback segment sizes larger.

* Free Buffer inspected

This is a measure of what many buffers on the LRU list was 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 a server process is unable to find a free buffer and

have requested the database writer to make free buffers by writing out dirty buffers. A

Dirty buffer is a buffer whose contents has been modified. Dirty buffers is freed

For reuse when dbwn have written the blocks to disk.

To resolve the contention, dbwn have to make buffers available faster for

Overwriting. To achieve this, examine ways of speeding up the write process. This

Event is also a indication that, the buffer cache is too small. Examine the hit ratios

For the buffer caches to determine if the cache should is resized.

If This event occurs frequently, examine the session waits for DBWN to determine

Whether there is anything delaying dbwn.

Causes

Dbwn May is keeping up with writing dirty buffers in the following situations:

-The I/O system is slow.

Solution:check that the files is 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 is 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 this dbwn spends most of its time cleaning out

Buffers for server processes.

Solution:increase the buffer cache size.

-the buffer cache is so large a dbwn process cannot free enough buffers

In the cache to satisfy requests.

Solution:decrease the buffer cache size or initialize more database writer

Processes.

The main wait event for BUFFER cache