Is there any query to find blocking sessions in the past? This is for Oracle database 12

Question

I want to know which sessions were blocking other sessions, but views like v$sessions and v$locks only show me about active sessions, not sessions from other days.

I executed queries on v$session and v$lock, but didn´t show me the information that I require.

Answer 1

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.

Answer 2

Here is a query that gives us a list of blocking sessions and the sessions that they are blocking:

select
   blocking_session,
   sid,
   serial#,
   wait_class,
   seconds_in_wait
from
   v$session
where
   blocking_session is not NULL
order by
   blocking_session;

BLOCKING_SESSION	SID	SERIAL#	WAIT_CLASS	SECONDS_IN_WAIT
148	135	61521	Idle	64