How to diagnose the reason for a wait event in a past time period

1. View a blocking session for a waiting event
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/

To view an SQL statement waiting for an event
Sql> Select Sql_text from V$sql where sql_id= ' 8duehra3kdx62 ';

2. View the blocking session that caused the session waiting above
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

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/storage/