Tracking Historical Locks and SQL

Quick script to find old locks and waits and their corresponding SQL_ID:

select event,sum(time_waited)/1000000 s,session_id,module,BLOCKING_SESSION, sql_id
from  dba_hist_active_sess_history
where EVENT like 'enq%' and snap_id in (select snap_id from dba_hist_snapshot where BEGIN_INTERVAL_TIME > to_timestamp('&startdate','dd-mon-yy hh24:mi:ss')
and BEGIN_INTERVAL_TIME <= to_timestamp('&enddate','dd-mon-yy hh24:mi:ss'))
group by event,session_id,module,BLOCKING_SESSION, sql_id
order by 2

Output:

EVENT                 S   SESSION_ID MODULE       BLOCKING_   SQL_ID
                                                  SESSION 
--------------------- -   ---------- ------------ ---------- ----------------
enq: TM - contention  12    539 JDBC Thin Client  95          45xdtpjd3xwpq
enq: TM - contention  87    632 JDBC Thin Client  95          45xdtpjd3xwpq
enq: TM - contention  123   277 JDBC Thin Client  95          45xdtpjd3xwpq
enq: TM - contention  221   454 JDBC Thin Client  95          45xdtpjd3xwpq
enq: TM - contention  432    10 JDBC Thin Client  95          45xdtpjd3xwpq
enq: TM - contention  559    12 JDBC Thin Client  95          45xdtpjd3xwpq
enq: TM - contention  631    95 DBMS_SCHEDULER    537         c3x45z1anf433

Leave a Reply