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

Info about SCAN

In my company, we have more than 1000 databases and sometimes is very hard to manage all of them.

So I created some scripts that help me to manage some things.

One of this is the information on the SCANs

The SCAN port, the SCAN name and the network interface where the SCAN listeners are configured are different. So, in order to check the information of the SCAN configuration, I wrote following script

The output is something like this

###################################################################
########################### SCAN info #############################
###################################################################
Scan Name     :  nodecl-scan
Scan Port     :  1555
Dedined on    :  Network: 1
Subnet IPv4   :  10.10.226.0/255.255.254.0
Network Card  :  eth0
LISTENER_SCAN1:  10.10.227.38/eth0:2 (ONLINE on node01)
LISTENER_SCAN2:  10.10.227.39/eth0:3 (ONLINE on node02)
LISTENER_SCAN3:  10.10.227.40/eth0:3 (ONLINE on node01)
###################################################################

As you can see, the output shows also the interface where the listener is bound. In this case, on the node01, the listeners are on eth0:2 and eth0:3 with 10.10.267.38 and 10.10.267.40, respectively. On the node02 instead, the SCAN listener 2, with IP address 10.10.267.39, is on eth0:3

In case of the crs is down, the script returns the following error

>>>>> CRS is not working correctly <<<<<

>>>>> Check the crs status <<<<<

CRS-6750: unable to get the active version CRS-6752: Active version query failed