Blocking & Locks

Identify sessions holding a lock that another session is currently requesting.

SELECT
    blocker.sid AS blocker_sid,
    blocker.serial# AS blocker_serial,
    blocker.username AS blocker_username,
    waiter.sid AS waiter_sid,
    waiter.serial# AS waiter_serial,
    waiter.username AS waiter_username,
    waiter.event,
    waiter.seconds_in_wait
FROM v$lock blocker_lock
JOIN v$lock waiter_lock
    ON waiter_lock.id1 = blocker_lock.id1
   AND waiter_lock.id2 = blocker_lock.id2
JOIN v$session blocker
    ON blocker.sid = blocker_lock.sid
JOIN v$session waiter
    ON waiter.sid = waiter_lock.sid
WHERE blocker_lock.block = 1
  AND waiter_lock.request > 0
  AND blocker_lock.sid <> waiter_lock.sid
ORDER BY waiter.seconds_in_wait DESC;

Review sessions currently reporting a blocking session and the wait event associated with the blocked workload.

SELECT
    sid,
    serial#,
    username,
    status,
    sql_id,
    blocking_session,
    blocking_session_status,
    event,
    wait_class,
    seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;

Review the immediate blocker and final blocker information to understand whether multiple sessions are waiting behind one root.

SELECT
    sid,
    serial#,
    username,
    sql_id,
    blocking_session,
    blocking_session_status,
    final_blocking_session,
    final_blocking_session_status,
    event,
    wait_class
FROM v$session
WHERE blocking_session IS NOT NULL
   OR final_blocking_session IS NOT NULL
ORDER BY final_blocking_session, blocking_session, sid;

Compare locks held and locks requested by sessions using the lock type, mode and resource identifiers.

SELECT
    l.sid,
    s.serial#,
    s.username,
    l.type,
    l.id1,
    l.id2,
    l.lmode,
    l.request,
    l.ctime,
    l.block
FROM v$lock l
JOIN v$session s
    ON s.sid = l.sid
WHERE l.block = 1
   OR l.request > 0
ORDER BY l.block DESC, l.request DESC, l.ctime DESC;

Map transaction locks to database objects to identify the table or object associated with the contention.

SELECT
    lo.session_id AS sid,
    s.serial#,
    s.username,
    lo.oracle_username,
    lo.locked_mode,
    lo.object_id,
    o.owner,
    o.object_name,
    o.object_type
FROM v$locked_object lo
JOIN v$session s
    ON s.sid = lo.session_id
JOIN dba_objects o
    ON o.object_id = lo.object_id
ORDER BY o.owner, o.object_name, lo.session_id;

Identify the SQL currently associated with a blocking session and continue the investigation into what is holding the resource.

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.sql_id,
    s.prev_sql_id,
    s.event,
    s.wait_class,
    q.plan_hash_value,
    q.executions,
    q.elapsed_time,
    q.cpu_time,
    q.buffer_gets,
    q.sql_text
FROM v$session s
LEFT JOIN v$sql q
    ON q.sql_id = s.sql_id
WHERE s.sid = &blocker_sid;