如何诊断过去某个时间段的等待事件原因？



如何诊断过去某个时间段的等待事件原因？

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

    /