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.....

The script currently has two inputs BEGIN_SNAP_ID and END_SNAP_ID which can be used to restrict the time period to a given range of snapshots. To restrict based on time as opposed to snapshots, we could use the dba_hist_active_sess_history.sample_time timestamp column.

and blocker.session_serial# = blocked.blocking_session_serial#

Nice find @Glireon! I have updated the code to include the serial# condition as well.

FYI, I have updated oracle_blocker_blocked_sessions.sql to use the v$session_blockers view. The oracle_blocker_blocked_sessions.sql script is useful for finding current/active blockers as opposed to this script that looks through the history for blocking.

@satish-mpr if you look at my first comment here it explains the two inputs or BEGIN_SNAP_ID and END_SNAP_ID. These are the AWR snapshot IDs which can be pulled from the dba_hist_snapshots view.

Thanks David .Its really an awesome script

Great

Thanks for the script , the concept looks great , however the script takes forever to finish on my 12c database between 2 hours snapshots

is there any specific release restriction ?

@Enrique2k I suspect it is based on the amount sample data the query needs to process. My small system took ~1m35s to process the previous hour's sample data and 2m21s for the past two hours. The amount of enq: TX - row lock contention would also likely change the execution time.

Thanks for this amazing script. Could you please add a column that shows "Blocking session SQL" , "Blocking session SQL text" as well? Right now it shows SQL details for blocked session. Thanks in advance .

@ranjanmb you can add in the following column to see the SQL from the blocker session

blocker_sql.sql_text      as blocker_sql_text

The main reason I do not select the SQL from the blocker session is that it is often not the SQL causing the block. Most of the time the blocker session is running a lot of SQL within a transaction and the statement causing the block was a previous SQL within the transaction. The SQL that is displayed is often a subsequent SQL as the transaction has moved on from the SQL that is causing the block. Only the current SQL running within the transaction at the time the data is collected will be shown. I have often seen a SELECT statement as the blocker SQL, which does not give us any information as SELECT statement do not cause row level locking.

It is more useful to see the SQL that is blocked as we know the exact table and ROWID. We then know that the blocker session at some point within its transactions performed a change on that same ROWID. Applications logs are then useful to determine what the blocker session was doing and why it was holding that lock for so long.

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.