oracle9i 等待事件buffer busy waits的诊断，分析与解决的思路

这篇是关于oracle等待事件buffer busy waits的出现在数据库，影响数据库性能的时候，

总结的一个思路：

 

解决思路：
1.查询等待事件的p1,p2,p3参数代表的属性:

  select * from v$event_name where name='buffer busy waits'

 

2.获取等待事件的参数属性的具体值:
select event, sid,count(*) num_waits,p1 "file#", p2 "block#", p3 "reason code"
       from v$session_wait
       where event = 'buffer busy waits'
       group by event,p1,p2,p3,sid;

 
3.确定是否热点块频繁被操作：
select * from v$waitstat where count>0 and class='data block';

4.找出导致等待事件的SQL：
select sql_text
  from v$sql t1, v$session t2, v$session_wait t3
 where t1.address = t2.sql_address
   and t1.hash_value = t2.sql_hash_value
   and t2.sid = t3.sid
   and t3.event = 'buffer busy waits';
  
  
5.找出热点块所在的表段:（这里只涉及到热点block）
select a.segment_type || ' block' class,
       a.segment_type,
       a.segment_name,
       a.partition_name
  from dba_extents a, v$session_wait b
 where a.file_id = b.p1
   and b.p2 between a.block_id and a.block_id + a.blocks - 1
   and b.event = 'buffer busy waits'
   and not exists (select 1
          from dba_segments
         where header_file = b.p1
           and header_block = b.p2);

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