Who is blocking whom and what are they doing?

A fairly common question I get as a DBA is tracking down which sessions are blocking others. Fortunately the v$session view has the blocking session (as of 10.1) and instance of that session (as of 10.2) included. Since a blocker could itself be blocked by yet another session, I use a hierarchical query to determine the root blocker. Another useful feature of v$session is the inclusion of the row_wait_* columns and sql_id. With these I can find the specific row of an object in contention as well as the current statement of each session.

It is important to note, the current statement of blocker is not necessarily the statement that caused a blocking lock in another session.

I like to use a query like this…

WITH
    blockers_and_blockees
    AS
        (SELECT ROWNUM rn, a.*
           FROM gv$session a
          WHERE blocking_session_status = 'VALID'
             OR (inst_id, sid) IN (SELECT blocking_instance, blocking_session
                                     FROM gv$session
                                    WHERE blocking_session_status = 'VALID'))
    SELECT LPAD(' ', 3 * (LEVEL - 1)) || sid || DECODE(LEVEL, 1, ' root blocker')
               blocked_session,
           inst_id,
           event,
              TO_CHAR(FLOOR(seconds_in_wait / 3600), 'fm9900')
           || ':'
           || TO_CHAR(FLOOR(MOD(seconds_in_wait, 3600) / 60), 'fm00')
           || ':'
           || TO_CHAR(MOD(seconds_in_wait, 60), 'fm00')
               time_in_wait,
           username,
           osuser,
           machine,
           (SELECT owner || '.' || object_name
              FROM dba_objects
             WHERE object_id = b.row_wait_obj#)
               waiting_on_object,
           CASE
               WHEN row_wait_obj# > 0
               THEN
                   DBMS_ROWID.rowid_create(1,
                                           row_wait_obj#,
                                           row_wait_file#,
                                           row_wait_block#,
                                           row_wait_row#)
           END
               waiting_on_rowid,
           (SELECT sql_text
              FROM gv$sql s
             WHERE s.sql_id = b.sql_id AND s.inst_id = b.inst_id AND s.child_number = b.sql_child_number)
               current_sql,
           status,
           serial#,
           (SELECT spid
              FROM gv$process p
             WHERE p.addr = b.paddr AND p.inst_id = b.inst_id)
               os_process_id
      FROM blockers_and_blockees b
CONNECT BY PRIOR sid = blocking_session AND PRIOR inst_id = blocking_instance
START WITH blocking_session IS NULL;

In 11.2.0.3 I did encounter a bug producing wrong results in the hierarchical query. As a workaround, on that version I would materialize v$session prior to inclusion within the blockers_and_blockees view. I’m unaware of a patch to address the bug, other than upgrading to 11.2.0.4 or higher.