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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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:
|
1 2 3 4 |
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.