Oracle blocker and blocked session information from history.

oracle_blocker_blocked_sess_history.sql

select distinct
    -- Snapshot ID
    min(blocked.snap_id)      as first_snap_id,
    max(blocked.snap_id)      as last_snap_id,

    -- Sample ID and Time
    min(blocked.sample_id)    as first_sample_id,
    min(blocked.sample_id)    as last_sample_id,
    to_char(
        min(blocked.sample_time),
        'YYYY-MM-DD HH24:MI:SS'
    )                         as first_sample_time,
    to_char(
        max(blocked.sample_time),
        'YYYY-MM-DD HH24:MI:SS'
    )                         as last_sample_time,

    -- Session causing the block
    blocker.instance_number   as blocker_instance_number,
    blocker.machine           as blocker_machine,
    blocker.program           as blocker_program,
    blocker.session_id        as blocker_sid,
    blocker_user.username     as blocker_username,

    ' -> '                    as is_blocking,

    -- Sesssion being blocked
    blocked.instance_number   as blocked_instance_number,
    blocked.machine           as blocked_machine,
    blocked.program           as blocked_program,
    blocked.session_id        as blocked_sid,
    blocked_user.username     as blocked_username,
    blocked.session_state     as blocked_session_state,
    blocked.event             as blocked_event,
    blocked.blocking_session  as blocked_blocking_session,
    blocked.sql_id            as blocked_sql_id,
    blocked.sql_child_number  as blocked_sql_child_number,
    sys_obj.name              as blocked_table_name,
    dbms_rowid.rowid_create(
        rowid_type    => 1,
        object_number => blocked.current_obj#,
        relative_fno  => blocked.current_file#,
        block_number  => blocked.current_block#,
        row_number    => blocked.current_row#
    )                         as blocked_rowid,
    to_char(
        blocked_sql.sql_text
    )                         as blocked_sql_text
from
    dba_hist_active_sess_history blocker
    inner join
    dba_hist_active_sess_history blocked
        on blocker.session_id = blocked.blocking_session
        and blocker.session_serial# = blocked.blocking_session_serial#
    inner join
    sys.obj$ sys_obj
        on sys_obj.obj# = blocked.current_obj#
    inner join
    dba_users blocker_user
        on blocker.user_id = blocker_user.user_id
    inner join
    dba_users blocked_user
        on blocked.user_id = blocked_user.user_id
    left outer join
    dba_hist_sqltext blocked_sql
        on blocked_sql.sql_id = blocked.sql_id
    left outer join
    dba_hist_sqltext blocker_sql
        on blocker_sql.sql_id = blocker.sql_id
where
    blocked.snap_id between BEGIN_SNAP_ID and END_SNAP_ID
and
    blocked.event = 'enq: TX - row lock contention'
group by
    blocker.instance_number,
    blocker.machine,
    blocker.program,
    blocker.session_id,
    blocker_user.username,
    ' -> ',
    blocked.instance_number,
    blocked.machine,
    blocked.program,
    blocked.session_id,
    blocked_user.username,
    blocked.session_state,
    blocked.event,
    blocked.blocking_session,
    blocked.sql_id,
    blocked.sql_child_number,
    sys_obj.name,
    dbms_rowid.rowid_create(
        rowid_type    => 1,
        object_number => blocked.current_obj#,
        relative_fno  => blocked.current_file#,
        block_number  => blocked.current_block#,
        row_number    => blocked.current_row#
    ),
    to_char(blocked_sql.sql_text)
order by
    first_sample_id;

Dear Sir, Awesome script, I was just curious if there is a way to find the root blocker details, at any given point of time or between a snapshot range: if you add that terminology to the above script, would be really awesome.....

Sorry for the long delay, just noticing now that I had not responded. It is often hard to determine the cause of the block within the blocker session. The blocker session is more than likely a transaction from an application with many SQL statements. One of the statements in the blocker session is holding a lock on a row and subsequent statements need to execute before the transaction completes to release the lock. I look to application logs to determine the SQL and SQL times from the application point of view. I have seen many cases where an update is performed in the blocker session and later on in the same transaction a long running SELECT statement occurs causing the lock to be held open for too long.

It really helps if the application logs Oracle session IDs. This really helps in identifying which statements belong to which session. From the Oracle side we can determine the "blocker" SID and "blocked" SID to help narrow down the cause from the application logs.

would be nice to have the duration as well