Find and resolve Blocking Session

Hi Everyone,

In the life of Oracle/Oracle Apps DBA, we usually fall in the pit of blocking session. First we need to find whether any blocking session is happening by the following query:-

select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;

If there is any blocking session, we must check the "second_in_wait" as well as the sid which is blocking. If the session wait is higher and the its inactive for long time, then we can get rid of that session by:-

alter system kill session 'sid,serial#' immediate;
for eg: alter system kill session '1140,188' immediate;

Other Useful Queries related with Locks & sessions:-

SELECT o.object_name,
v.session_id sid,
v.oracle_username,
decode(l.type,'TM', 'DML enqueue (TM)', 'TX', 'Transaction enqueue (TX)', 'UL', 'User supplied (UL)', l.type),
decode(l.lmode,0,'None',1,'Null',2,'Row Share',3,'Row Excl.',4,'Share',5,'S/Row Excl.',6,'Exclusive',to_char(l.lmode,'990')) holding,
decode(l.request,0,'None',1,'Null',2,'Row Share',3,'Row Excl.',4,'Share',5,'S/Row Excl.',6,'Exclusive',to_char(l.request,'990')) wanting,
l.block,
s.terminal,
s.machine
FROM sys.v_$locked_object v,
sys.v_$lock l,
sys.dba_objects o,
sys.v_$session s
WHERE l.sid = v.session_id
AND s.sid = v.session_id
AND v.object_id = o.object_id;


select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held, s.event
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
and l.sid = s.sid
and v.session_id = l.sid
and s.process = v.process
order by oracle_username
, session_id


select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));

select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#
from v$active_session_history ash, v$event_name enm
where ash.event#=enm.event#
-- and SESSION_ID=213 and SAMPLE_TIME>=(sysdate-&minute/(24*60));

Thanks
Punithavel

Welcome To The Oracle Applications Blog

Hi Apps DBAians,

Welcome to world of Oracle Apps DBA. We would like to post all the deatils pertaining to everyday activities like Cloning, patching, hot & cold backup, performance issues, etc.,

If you wanted to share anything, please leave a mail to orcleappsdba@gmail.com and it will posted with your credits.

Thanks in advance.
Punithavel
Abilash
Sampath