Lock Chains 02: GV$SESSION

In my previous post, I showed the chain of the locks using a query for just an instance. Recently Kaley Crum, an active member of the Orapub community, modified my statement so now you can use it in RAC configuration.

I only modified a little, 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

Note: How to read the output

1. The column BLOCKING_SESSION and LAST_LOCKED_SESSION are composed of 2 members separated by the colon. For example, on the first line, 1:1313 mean that on the instance 1 the session 1313 is the head of the lock chain where the last element is the session 609 on the instance 1.

2. In the PATH_EVENT column, the "session" and "instance" are related by an "at". For example the on the last line, 1377@1 mean that the session 1377 on the instance 2 is locking the session 1441 on the instance 2