Blocking Sessions in oracle database

Blocking Sessions:

Applies to: Oracle 9i/10g/11g

Blocking session occurs when one session acquired an exclusive lock on an object and doesn't release it,  another session (one or more) want to modify the same data.First session will block the second until it completes its job

Finding blocking sessions:

Using v$session:

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

Using v$lock:

select * from v$lock where block=1;select count(*) from gv$lock where block=1;select sid from v$lock where block=1;

Which Session is blocking?

select
(select username from v$session where sid=a.sid) blocker,
a.sid,' is blocking ',(select username from v$session where sid=b.sid) blockee,b
.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

Finding the query of the sessions:

SELECT a.sql_text, b.sql_hash_value
FROM   v$sqltext a,
       v$session b
WHERE  a.address = b.sql_address
AND    a.hash_value = b.sql_hash_value
AND    b.sid = &1
ORDER BY a.piece;

Complete Details of blocking sessions:

select distinct
a.sid "waiting sid"
, d.sql_text "waiting SQL"
, a.ROW_WAIT_OBJ# "locked object"
, a.BLOCKING_SESSION "blocking sid"
, c.sql_text "SQL from blocking session"
from v$session a, v$active_session_history b, v$sql c, v$sql d
where a.event='enq: TX - row lock contention'
and a.sql_id=d.sql_id
and a.blocking_session=b.session_id
and c.sql_id=b.sql_id
and b.CURRENT_OBJ#=a.ROW_WAIT_OBJ#
and b.CURRENT_FILE#= a.ROW_WAIT_FILE#
and b.CURRENT_BLOCK#= a.ROW_WAIT_BLOCK#

Find the Unix process id from SID:

select spid 
from  v$process 
where background is null 
and     addr in (select paddr
                        from   v$session
                        where  sid=&session_id);

Find SID from SPID: (Not very much required here)

select s.username, s.status,  s.sid,     s.serial#,
      p.spid,     s.machine, s.process, s.lockwait
from   v$session s, v$process p
where  s.process  = '&unix_pid'
and    s.paddr    = p.addr;


Blocking Session has to be released by taking concurrence with application team:

How to release a blocking Session:

Ge the SID details:

select sid,SERIAL#,status,username from v$session where sid=<Blocking Session>;
select SID,MACHINE,TERMINAL,PROGRAM,MODULE from v$session where sid=<Blocking Session>;

Disconnecting the Session:

alter system disconnect session '<SID>,<Serial#>' IMMEDIATE;

Kill the Server Process:

kill -9 <Unix Process Id from SID>

Corrective Action:

For a blocking session only two corrective actions:
    1. Disconnect the blocking session
    2. Wait for completing the blocking session

Preventive Action:

Application has to be designed/corrected as no two or more sessions required the same data at the same time to be modified.

No comments:

Post a Comment