Find out more about current Buffer Busy Waits
 
Short desc Find out more about current Buffer Busy Waits
Os Any
Tested on 10.2.x
Remarks The Histogram (first SQL is since the database started. The rest shows recent results. If the SQL text is not showing this means it is no longer in the SQL area. This means that either your SQL area is too small or you waited too long since the problem happened.
Submitted by Ajith Pathiyil
Date March 2008



The following set of scripts will show you if you are currently suffering from Buffer Busy Waits (BBW) problems, the top 5 problomatic BBW blocks and which SQL is causing them, information about the top waited on block and when was the top waited on block had the BBW and which SQL waited for it.
 
 
 Script source code
column object_name format a20
column owner format a20
column object_type format a20
column sample_time format a27
column sql_text format a50
set pages 40
set long 1000
set lines 120
set ver off

spool bbw
prompt Histogram of Buffer Busy Wait
select WAIT_TIME_MILLI, WAIT_COUNT, round((WAIT_COUNT/tot) * 100, 2) pct
from v$event_histogram,
(select sum(WAIT_COUNT) tot
from v$event_histogram
where event = 'buffer busy waits'
)
where event = 'buffer busy waits'
/

prompt top 5 buffer busy waits
select sql_text, seconds_wait, waits, p1 file#, p2 block#
from v$sql ,
( select * from
(select p1 , p2 , count(*) waits, sum(time_waited)/1000000 seconds_wait, sql_id
from V$ACTIVE_SESSION_HISTORY
where event= 'buffer busy waits'
group by p1, p2, sql_id
order by 4 desc)
where rownum < 6
) a
where a.sql_id = v$sql.sql_id(+)
/
column file# new_value fv
column block# new_value bv
Prompt top waited on block information
select OBJECT_NAME, OBJECT_TYPE, OWNER,
seconds_wait, waits, p1 file#, p2 block#
from dba_objects ,
( select * from
(select p1 , p2 , count(*) waits, sum(time_waited)/1000000 seconds_wait,
CURRENT_OBJ#
from V$ACTIVE_SESSION_HISTORY
where event= 'buffer busy waits'
group by p1, p2, CURRENT_OBJ#
order by 4 desc)
where rownum < 2
) a
where current_obj# = object_id
/
prompt Times of Break Down Of top waited on Block
select sample_time, TIME_WAITED/1000000 seconds_wait, sql_text
from V$ACTIVE_SESSION_HISTORY outer left join v$sql using(sql_id)
where event= 'buffer busy waits'
and CURRENT_FILE# = nvl(to_number('&fv'), -1)
and CURRENT_BLOCK# = nvl(to_number('&bv'), -1)
order by 1
/
spool off