Lockall

Real-time locks

Lists sessions currently holding locks, along with the locked object and the lock mode (Row-S, Row-X, Exclusive, and so on). This is the first script to run when someone reports slowness or an active blocking hang.

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
COLUMN module FORMAT A30

SELECT lo.session_id AS sid,
       s.serial#,
       NVL(lo.oracle_username, '(oracle)') AS username,
       o.owner AS object_owner,
       o.object_name,
       DECODE(lo.locked_mode,
              0, 'None',
              1, 'Null (NULL)',
              2, 'Row-S (SS)',
              3, 'Row-X (SX)',
              4, 'Share (S)',
              5, 'S/Row-X (SSX)',
              6, 'Exclusive (X)',
              lo.locked_mode) AS locked_mode,
       s.module,
       lo.os_user_name
FROM   gv$locked_object lo
       JOIN dba_objects o ON o.object_id = lo.object_id
       JOIN gv$session s ON lo.session_id = s.sid AND lo.inst_id = s.inst_id
ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14
SET VERIFY ON

Reference: Oracle E-Business Suite Performance Guide (Doc ID 1672174.1)

Historical view (AWR)

Investigates blocking that already happened, using dba_hist_active_sess_history. Useful when a user reports a slowdown that isn't occurring anymore. Adjust dbid, instance_number, and the snap_id range to the environment and time window under investigation.

SELECT
   s.session_id,
   s.blocking_session,
   s.session_serial#,
   s.sql_id,
   s.wait_class,
   s.event,
   s.p1text,
   s.p1,
   s.p2text,
   s.p2,
   s.p3text,
   s.p3,
   o.object_type,
   o.object_name,
   s.current_obj#,
   s.current_file#,
   s.current_block#,
   s.current_row#,
   s.program,
   s.module,
   s.action,
   s.client_id,
   s.machine,
   COUNT(*)*10 approx_wait_secs, -- if 10 seconds is reported, it could be a lot less
   MIN(s.sample_time) start_sample_time,
   MAX(s.sample_time) end_sample_time
FROM dba_hist_active_sess_history  s,
     dba_hist_seg_stat_obj o 
WHERE s.dbid = 1073788239
AND s.instance_number = 1
AND s.snap_id BETWEEN 13209 and 189165
AND o.dbid (+) = s.dbid
AND o.obj# (+) = s.current_obj#
AND s.blocking_session IS NOT NULL
AND s.event IN 
    ('enq: TX - row lock contention'
    ,'enq: TM - contention'
    ,'enq: UL - contention'
    ,'enq: TX - allocate ITL entry')
GROUP BY s.session_id, s.blocking_session, s.session_serial#, s.sql_id, s.wait_class, s.event, s.p1text, s.p1, s.p2text, s.p2, s.p3text, s.p3, o.object_type, o.object_name, s.current_obj#, s.current_file#, s.current_block#, s.current_row#, s.program, s.module, s.action, s.client_id, s.machine
ORDER BY COUNT(*) DESC;

Blocking chain (hierarchy)

Builds the full tree of who is blocking whom using CONNECT BY, all the way to the root blocker (ultimate blocker). Essential when the blocking isn't direct, but a chain across several sessions.

SELECT 
   level,
   sample_time,
   session_id blocked_sid,
   CONNECT_BY_ROOT blocking_session ultimate_blocker_sid,
   sys_connect_by_path(blocking_session,'/')|| '/' || session_id blocking_path 
FROM
   (-- Blocked sessions
    SELECT
       s.session_id,
       s.blocking_session,
       s.sample_time
    FROM dba_hist_active_sess_history s 
    WHERE s.dbid = 1073788239
    AND s.instance_number = 1
    AND s.snap_id BETWEEN 13209 and 189165
    AND s.blocking_session IS NOT NULL
    AND s.event IN 
        ('enq: TX - row lock contention'
        ,'enq: TM - contention'
        ,'enq: UL - contention'
        ,'enq: TX - allocate ITL entry')
    UNION
    -- Blocking sessions
    SELECT s.session_id,
           s.blocking_session,
           s.sample_time
    FROM dba_hist_active_sess_history s 
    WHERE s.dbid = 3245563391
    AND s.instance_number = 1
    AND s.snap_id BETWEEN 189164 and 189165
    AND s.blocking_session IS NULL
    AND s.event IN
        ('enq: TX - row lock contention'
        ,'enq: TM - contention'
        ,'enq: UL - contention'
        ,'enq: TX - allocate ITL entry')
    AND EXISTS
       (SELECT 'exists' 
        FROM dba_hist_active_sess_history bs 
        WHERE bs.dbid = 3245563391
        AND bs.instance_number = 1
        AND bs.snap_id BETWEEN 189164 and 189165
        AND bs.blocking_session = s.session_id
        AND bs.sample_time = s.sample_time
        AND bs.blocking_session IS NOT NULL
        AND bs.event IN 
            ('enq: TX - row lock contention'
            ,'enq: TM - contention'
            ,'enq: UL - contention'
            ,'enq: TX - allocate ITL entry')
       )
   )
CONNECT BY NOCYCLE PRIOR session_id = blocking_session 
                   AND PRIOR sample_time = sample_time 
ORDER BY level DESC, blocked_sid, sample_time;

Row lock waits by segment (AWR)

Relates the row_lock_waits delta per segment across a snapshot window. Useful for identifying which tables concentrate the most row-level contention over time, not just at a single point.

SELECT P.snap_id,
  P.begin_interval_time,
  O.owner,
  O.object_name,
  O.subobject_name,
  O.object_type,
  S.row_lock_waits_delta
FROM dba_hist_seg_stat S,
  dba_hist_seg_stat_obj O,
  dba_hist_snapshot P
WHERE S.dbid               = O.dbid
AND S.ts#                  = O.ts#
AND S.obj#                 = O.obj#
AND S.dataobj#              = O.dataobj#
AND S.snap_id              = P.snap_id
AND S.dbid                 = P.dbid
AND S.instance_number      = P.instance_number
AND S.row_lock_waits_delta > 0
AND P.snap_id BETWEEN      13207 and 13217 
ORDER BY 1,3,4;