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.