教你快速掌握基于“等待事件”的性能诊断

来源：赛迪网    作者：yixiu

等待事件有两种类型：空闲事件（idle event）与非空闲事件（non-idle event）。

空闲事件主要指oracle正在等待某种工作，常见的空闲等待事件有：client message、null event、pipe get、pmon/smon timer、rdbms rpc message及sql*net等。

非空闲等待事件：buffer busy waits、db file scattered read、db file sequential read、enqueue、free buffer waits、latch free、log file sync、log file paralle write等。

有人要问什么是瓶颈？当我们熟悉了系统的等待事件，就可以把握问题的关键，并且能够用相应的方法去处理阻塞系统的瓶颈，请注意一定不要随便的进行优化，否则很可能使结果雪上加霜，我们可以通过v$system_event获取系统总的等待情况，然后通过v$session_event查看系统中session的等待情况，最后通过v$session_wait定位瓶颈对象。v$session_wait是会话级的，它包含session的实时信息，最重要的是：它显示了等待事件与相应资源的更深入信息，可确定出产生瓶颈的类型及其对象。

v$session_wait的p1、p2、p3告诉我们等待事件的具体含义，如果wait event是db file scattered read，p1=file_id/p2=block_id/p3=blocks，然后通过dba_extents即可确定出热点对象；如果是latch free的话,p2为闩锁号，它指向的是v$latch。

◆求等待事件及其对应的latch

col event format a32
col name format a32
select sid,event,p1 as file_id, p2 as "block_id/latch", p3 as blocks,l.name
from v$session_wait sw,v$latch l
where event not like '%SQL%' and event not like '%rdbms%' 
and event not like '%mon%' and sw.p2 = l.latch#(+);

◆求等待事件及其热点对象

col owner format a18
col segment_name format a32
col segment_type format a32
select owner,segment_name,segment_type
from dba_extents
where file_id = &file_id and &block_id between block_id 
and block_id + &blocks - 1;

◆结合以上两条sql，同时显示latch及热点对象(注释：速度较慢)

select sw.sid,event,l.name,de.segment_name
from v$session_wait sw,v$latch l,dba_extents de
where event not like '%SQL%' and event not like '%rdbms%' 
and event not like '%mon%' and sw.p2 = l.latch#(+) 
and sw.p1 = de.file_id(+) and p2 between 
de.block_id and de.block_id + de.blocks - 1;

◆如果是非空闲等待事件，通过等待会话的sid可以求出该会话在执行的sql

select sql_text 
from v$sqltext_with_newlines st,v$session se
where st.address=se.sql_address and st.hash_value=se.sql_hash_value
and se.sid =&wait_sid order by piece;

注释：DBA通过等待事件找出系统中消耗资源较严重的sql是常用的手段，唯一的缺点就是过程比较烦琐，由于session是动态的、瞬息万变、不可捕获，当你想捕获时，该session可能已经释放，但这种捕获是很有针对性的；也可以通过对v$sql或v$sqlarea进行过滤，找出存在性能问题的sql,长时间地对v$sql进行监控，并对捕获的sql进行优化处理，可以在很大程度上解决系统的性能问题。