Translation

The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

giovedì, agosto 16, 2018

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