Hierarchical Blocking Session List

Hi,

Today, I needed to list all blocking sessions hierarchically. As you know, all information about sessions (and blocking sessions) are in gv$session (or just v$session if you are using single instance) view. I wanted to see who is blocking who and blocker at the top. you can write simple connect by (or with clause) query but it will list blockers at the bottom so I just developed it a little bit. if I remember correctly there were a small script under rdbms/admin but it was creating some tables etc etc. I just want to get it using a simple sql statements.

here is the sql:

with blockers as (
    select /*+ materialize */ blocking_session from gv$session where blocking_session is not null
  ),
  blocker_hierarchy as (
    select /*+ materialize */ sid, blocking_session, lpad(sid, 10 + level*2, ' '), CONNECT_BY_ISLEAF leaf
    from   gv$session
    connect by nocycle prior sid = blocking_session and prior inst_id = inst_id
    start with sid in (select blocking_session from blockers)
  ),
  Main_Blockers as (
    select username, sid, blocking_session, 
           lpad(' ', (level-1)*2, ' ') ||Decode(CONNECT_BY_ROOT sid, sid, 'BLOCKER', 'BLOCKED') type, 
           'SID: ' || lpad('>', (level-1)*3, '-') || rpad(to_char(sid), 6, ' ') || 
           'User:' || username || 
           '  Current SQL: ' ||(Select substr(Sql_Text, 1, 40) from v$sqlarea where sql_id = s.sql_id) Blocking_info
    from   gv$session s
    connect by nocycle prior sid = blocking_session and prior inst_id = inst_id
    start with sid in (select sid from blocker_hierarchy 
                       where  leaf = 0 
                         and  blocking_session is null 
                         and  type <> 'BACKGROUND')
    order siblings by sid)
select * from Main_Blockers;

with blockers as (

select /*+ materialize */ blocking_session from gv$session where blocking_session is not null

blocker_hierarchy as (

select /*+ materialize */ sid, blocking_session, lpad(sid, 10 + level*2, ' '), CONNECT_BY_ISLEAF leaf

from gv$session

connect by nocycle prior sid = blocking_session and prior inst_id = inst_id

start with sid in (select blocking_session from blockers)

Main_Blockers as (

select username, sid, blocking_session,

lpad(' ', (level-1)*2, ' ') ||Decode(CONNECT_BY_ROOT sid, sid, 'BLOCKER', 'BLOCKED') type,

'SID: ' || lpad('>', (level-1)*3, '-') || rpad(to_char(sid), 6, ' ') ||

'User:' || username ||

' Current SQL: ' ||(Select substr(Sql_Text, 1, 40) from v$sqlarea where sql_id = s.sql_id) Blocking_info

from gv$session s

connect by nocycle prior sid = blocking_session and prior inst_id = inst_id

start with sid in (select sid from blocker_hierarchy

where leaf = 0

and blocking_session is null

and type <> 'BACKGROUND')

order siblings by sid)

select * from Main_Blockers;

first, I get all blockers’ sids (blockers data source in the query) then I use them to generate the hierarchy (blocker_hierarcy data source in the query) then I remove some blockers from the hierarchy because they are also blocked by some other sessions and will be listed under them.

for example, let’s say sid 10 is blocking 2 different sessions (sid 20 and 30) and also sid 30 is blocking some other session (sid 40). so this query will produce a result like this:

SID: 10  User:MUSTAFA  Current SQL: 
SID: -->20  User:DAVID  Current SQL: update t set d = 3 where d = 2
SID: -->30  User:STEPHAN  Current SQL: update t set d = 1 where d = 2
SID: ----->40    User:JOOST  Current SQL: update t set d = 1 where d = 3

SID: 10 User:MUSTAFA Current SQL:

SID: -->20 User:DAVID Current SQL: update t set d = 3 where d = 2

SID: -->30 User:STEPHAN Current SQL: update t set d = 1 where d = 2

SID: ----->40 User:JOOST Current SQL: update t set d = 1 where d = 3

you can see the hierarch in a visual way and the first 40 character of their currently running sql statements.

Hope this will be useful for you.

wish you healthy days.

Leave a Reply Cancel reply