oracle9i性能分析sql,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);

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