檢視造成等待事件的具體SQL語句

2015.06.04

先檢視存在的等待事件：
col event for a40
col WAIT_CLASS format a20
select sid,WAIT_CLASS,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait 
where event not like 'SQL%' and event not like 'rdbms%';

複製上面查到的等待事件，替換下面紅色字型。即可找到具體的SQL語句
col objn format a26
col otype format a10
select b.* ,a.sql_fulltext from v$sqlarea a,
(select * from (select 
    count(*), 
    sql_id, 
    nvl(o.object_name,ash.current_obj#) objn,
    substr(o.object_type,0,10) otype,
    CURRENT_FILE# fn,
         CURRENT_BLOCK# blockn
   from  v$active_session_history ash
       , all_objects o
   where event like 'latch: cache buffers chains'
     and o.object_id (+)= ash.CURRENT_OBJ#
   group by sql_id, current_obj#, current_file#,
                  current_block#, o.object_name,o.object_type
   order by  count(*) desc )where rownum <=15) b
where a.sql_id=b.sql_id;