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.