what is the difference between buffer busy waits events and free buffer waits in Oracle database?

select *
from
   v$system_event
where
   event like ‘%wait%’;
 

EVENT                       TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT

--------------------------- ----------- -------------- ----------- ------------

buffer busy waits                636528           1557      549700   .863591232

write complete waits               1193              0       14799   12.4048617

free buffer waits                  1601              0         622   .388507183

Buffer busy waits occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. This buffer busy wait condition can happen for either of the following reasons:

  • The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.
  • Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.

The Free Buffer Waits Oracle metric wait event indicates that a server process was unable to find a free buffer and has posted the database writer to make free buffers by writing out dirty buffers.

The solution for Buffer busy waits includes include database writer (DBWR) contention tuning, implementing Automatic Segment Storage Management (ASSM, a.k.a bitmap freelists), using reverse index and adding a missing index to reduce buffer touches.
The solution for Free buffer waits commonly includes tuning I\O, tuning DBWn, increasing the size of the buffer cache etc.

标签: none

评论已关闭