Donald K. Burleson Oracle Tips

Detecting Buffer Busy Waits

Buffer busy waits occur within Oracle when a task goes to fetch a data block, but it must wait because another task has control of the data block in the buffer.  A buffer busy wait is often caused by contention on an Oracle table header block because multiple tasks are waiting their turn to grab a freelist to place their new data rows.

The Oracle administrator is challenged to monitor the Oracle database and locate those time when the database is experiencing buffer busy waits.

The following STATSPACK script is very useful for detecting those times when the database has a high-level of buffer busy waits.

prompt

prompt

prompt ***********************************************************

prompt  Buffer Busy Waits may signal a high update table with too

prompt  few freelists.  Find the offending table and add more freelists.

prompt

prompt

prompt ***********************************************************

prompt

prompt

column buffer_busy_wait format 999,999,999

column mydate heading 'yr.  mo dy Hr.'

select

   to_char(snap_time,'yyyy-mm-dd HH24')      mydate,

   new.name,

   new.buffer_busy_wait-old.buffer_busy_wait buffer_busy_wait

from

   perfstat.stats$buffer_pool_statistics old,

   perfstat.stats$buffer_pool_statistics new,

   perfstat.stats$snapshot               sn

where

   snap_time > sysdate-&1

and

   new.name <> 'FAKE VIEW'

and

   new.snap_id = sn.snap_id

and

   old.snap_id = sn.snap_id-1

and

   new.buffer_busy_wait-old.buffer_busy_wait > 1

group by

   to_char(snap_time,'yyyy-mm-dd HH24'),

   new.name,

   new.buffer_busy_wait-old.buffer_busy_wait

;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: