DBA_HIST_ACTIVE_SESS_HISTORY and locking history information

The AWR historical DBA_HIST* views are a real treasure of information. This time, we are interested in historical information related to locking. the question is, who was blocking my session last night, within a given timeframe?

dba_hist_active_sess_history to the rescue! 

First step, we create our table as follows, to include only the timeframe we are interested in:

 SQL> create table my_hist_active_sess_history nologging parallel(degree 4)

            as select * from

           dba_hist_active_sess_history

           where SNAP_ID in ( select snap_id from dba_hist_snapshot where BEGIN_INTERVAL_TIME >            to_timestamp('06-jun-13 10:00:00','dd-mon-yy hh24:mi:ss')

           and BEGIN_INTERVAL_TIME <= to_timestamp('06-jun-13 11:00:00','dd-mon-yy hh24:mi:ss'));

The second step will be to query this table for the information we need; in my case, I'm looking for session which were blocked during this time:

SQL>

select event,sum(time_waited)/1000000 s,session_id,module,BLOCKING_SESSION

from my_hist_active_sess_history

where EVENT like 'enq%'

group by event,session_id,module,BLOCKING_SESSION

order by 2;

This is supposed to give us the blocking_session, together with the event and time waited.