Lock Chains 02: GV$SESSION

(Translated by ChatGPT on 2025/11/22)

In my previous post, I showed how to display a lock chain using a query that worked only for a single instance.

Recently, Kaley Crum — an active member of the Orapub community — updated my statement so it can now be used in a RAC environment.

I only made a few small adjustments to the output.

col BLOCKING_SESSION for a10
col LAST_LOCKED_SESSION for a10
col PATH_EVENT for a120
set lines 210
set pages 99
with
  snap as (
   select
        inst_id
      , sid
      , blocking_instance
      , blocking_session
      , sql_id
      , prev_sql_id
      , event
      , state
    from
      gv$session)
select
    to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') now
  , CONNECT_BY_ISCYCLE cycle
  , CONNECT_BY_ROOT inst_id ||':'||
    CONNECT_BY_ROOT sid blocking_session
  , inst_id ||':'||
    SID last_locked_session
  , SUBSTR(SYS_CONNECT_BY_PATH(
        DECODE(state,
                     'WAITING', sid|| '@' || inst_id ||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/'||event,
                                sid|| '@' || inst_id ||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/ON CPU'), ' -> ')
          ,5) path_event
 from snap 
where
  level > 1
connect by
  nocycle prior sid = blocking_session
       and prior inst_id = blocking_instance;

Picture 01 - Click to enlarge

How to read the output

1. The BLOCKING_SESSION and LAST_LOCKED_SESSION columns contain two values separated by a colon. For example, 1:1313 means that on instance 1, session 1313 is the head of the lock chain whose last element is session 609 on instance 1.

2. In the PATH_EVENT column, the session ID and instance number are connected with an “@” symbol.
   For example, 1377@1 on the last line means that session 1377 on instance 1 is blocking session 1441 on instance 2.