Unless you have another wait-based sampling monitoring tool, ASH (v$active_session_history
and dba_hist_active_sess_history
) is the only place to get that information. Those won't, unfortunately, tell you all that much about the sessions themselves, but they do provide potentially enough for investigating your issue.
You'll want to pay special attention to the columns event
and blocking_*
cols. current_obj#
joined to dba_objects
will tell you what object was involved. If you want a bit of info on the blocking session, you can take the blocking_session/blocking_session_serial#,blocking_inst_id
blocker key and requery ASH looking for these values in session_id/session_serial#,inst_id
key cols. You can then join user_id
to dba_users
to get the username and you can also see the program, module and machine in ASH. There is, unfortunately, no full-blown v$session
-style session history in Oracle (unless you custom build one yourself), so some details that could be useful (like osuser, logon time, etc..) you have to go without. You may not actually get any info on the blocker however if it took its lock out quickly and spent less than 10 seconds doing work before going idle. ASH will only record non-idle waits, so if a session is doing nothing, it doesn't show up in ASH, but it can still hold a lock.
As you query these, keep in mind that while v$active_session_history
(very recent activity) will show 1 row per second of non-idle wait (or CPU) per process, the on-disk version of that, dba_hist_active_sess_history
is a 1-out-of-10 sample of the former, so every row actually represents an average of 10 seconds of non-idle wait. If you need to see SQL, you can join on sql_id
to dba_hist_sqltext
for cursors no longer in the shared pool. ASH/AWR is only configured to keep data for so long, so if you wait too long (a few weeks or a month perhaps, depending on the setting) the information will no longer be available.