怎样诊断过去某个时间段的等待事件原因

1.查看某个等待事件的阻塞会话
select event,blocking_session,sql_id,count(*) from dba_hist_active_sess_history ash
   where sample_time>=to_timestamp('2013-06-24 12:25:00','yyyy-mm-dd hh24:mi:ss')
   and sample_time<=to_timestamp('2013-06-24 12:35:00','yyyy-mm-dd hh24:mi:ss')
   and event='cursor: pin S wait on X'
   group by event,blocking_session,sql_id  /

查看等待事件的sql语句
SQL> select sql_text from v$sql where sql_id='8duehra3kdx62';

2.查看导致上面的session 等待的 blocking session
SQL> select event,blocking_session,sql_id,count(*) from dba_hist_active_sess_history ash
   where sample_time>=to_timestamp('2013-06-24 12:25:00','yyyy-mm-dd hh24:mi:ss')
   and sample_time<=to_timestamp('2013-06-24 12:35:00','yyyy-mm-dd hh24:mi:ss')
   and ash.session_id=2
   group by event,blocking_session,sql_id

查看本栏目更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/storage/