Oracle: Resolving Blocking.

Finding Blocked Sessions In Oracle (All scripts verified in 11g running under Oracle Linux)
May 5, 2019

Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn’t release it before another sessions wants to update the same data. This will block the second until the first one has done its work. It mainly happens when a session issues an insert, update or delete command that changes a row. When the change occurs, the row is locked until the session either commits the change or rolls the change back.

Oracle provides several views to find the sessions that are the cause of blocking, like DBA_BLOCKERS, V$LOCK.

1: Here is query that will try to find locking sessions using V$LOCK view.

SELECT vs.username,
vs.osuser,
vh.sid locking_sid,
vs.status status,
vs.module module,
vs.program program_holding,
jrh.job_name,
vsw.username,
vsw.osuser,
vw.sid waiter_sid,
vsw.program program_waiting,
jrw.job_name,
‘alter system kill session ‘ || ””|| vh.sid || ‘,’ || vs.serial# || ”’;’  “Kill_Command”
FROM v$lock vh,
v$lock vw,
v$session vs,
v$session vsw,
dba_scheduler_running_jobs jrh,
dba_scheduler_running_jobs jrw
WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2
FROM v$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM v$lock
WHERE lmode = 0)

AND vh.id1 = vw.id1
AND vh.id2 = vw.id2
AND vh.request = 0
AND vw.lmode = 0
AND vh.sid = vs.sid
AND vw.sid = vsw.sid
AND vh.sid = jrh.session_id(+)
AND vw.sid = jrw.session_id(+);

The output, if there is any blocking activity, will give us session information, blocking and blocked SID. The last column
 gives the actual kill command to kill a hung session.  

2:  Find blocking sessions with v$session

SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
  
v$session s
WHERE
   blocking_session IS NOT NULL

3 Blocking sessions, a comprehensive query to get all available information. Run this query and you can immediately place a call to the user who is causing the blocking.

SELECT s1.username || ‘@’ || s1.machine
    || ‘ ( SID=’ || s1.sid || ‘ )  is blocking ‘
    || s2.username ||
‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$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 l1.id2 = l2.id2

These queries should help you to identify the cause of your blocking sessions!

To Kill a session:

Alter system kill session ‘23,7955’;   (example only)
Alter system kill session ‘sid, serial’;

4: Finding a session and killing a session
SELECT SID, SERIAL#, STATUS, SERVER, USERNAME
FROM V$SESSION

—–Where clause
–WHERE USERNAME = ‘tomf’

ALTER SYSTEM KILL SESSION ‘sid, serial’;

Prakash

Leave a Reply

Your email address will not be published. Required fields are marked *