Find blocking and blocked sessions

Sometimes a system may grind to a halt or a crawl waiting for database locks. Perhaps a user has left SQL*Plus, TOAD or a similar tool open with uncommitted changes or perhaps an error or oversight in the system itself is causing contention. Regardless the first step is to find out who is holding the locks. The following SQL shows the blocking sessions with a list of all the sessions they block. It works with RAC (the gv views are used rather than v views):

select s1.sid as blocker_sid,
       s1.username || '@' || s1.machine as blocker_user,
       wm_concat(s2.sid || ':' || s2.username || '@' || s2.machine) as blocked
  from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.block=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2
  group by s1.sid, s1.username, s1.machine;

The following SQL shows the most recent SQL executed by sessions holding blocking locks (if available):

select sid as blocker_sid, wm_concat(sql_text) as blocker_sql
  from (select s.sid, txt.sql_text
          from gv$sqltext txt, gv$session s, gv$lock l
          where txt.address = s.sql_address
          and s.sid = l.sid
          and l.block = 1
          order by s.sid, txt.piece)
  group by sid order by sid;

Finally the following SQL shows the SQL executed by the sessions that are waiting for blocking locks:

select sid as waiting_sid, wm_concat(sql_text) as waiting_sql
  from (select s.sid, txt.sql_text
          from gv$sqltext txt, gv$session s, gv$lock lb, gv$lock lw
          where txt.address = s.sql_address
          and s.sid = lw.sid
          and lw.id1 = lb.id1
          and lb.block = 1
          and lw.request > 0
          order by s.sid, txt.piece)
  group by sid order by sid;

Armed with this information the root cause is often obvious. The user can be told to commit or rollback, a session can be killed or perhaps an application issue resolved.

Advertisements