bffer busy waits events and free buffer waits
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.
评论已关闭