Blocking sessions in Oracle
4 min readBlocking sessions are a problem for the DBA which occur 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, rolls the change back or the user logs off the system.
Show Active Session:
col event format a24
col sid format 99999
set line 200
col machine format a30
col username format a10
col w format 999
col status format a2
col event format a30
col p3 format 999
col wt format 99999
col lt format 9999999
col param format a25
col SQL format a24
select b.sid,
substr(b.username,1,10) username,
decode(program, null, machine,replace(program,' (TNS V1-V3)','')||decode(machine,null,'@'||terminal)) machine,
substr((case
when a.event like 'enq%'
then a.event|| ':' || chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535)||'-'||bitand(a.p1, 65535)||':'||ROW_WAIT_FILE#||':'||ROW_WAIT_BLOCK#||':'|| ROW_WAIT_ROW#
else a.event
end),1,30) event,
a.p1||'/'||a.p2||'/'||a.p3 param,
a.wait_time w,a.SECONDS_IN_WAIT WT,b.sql_hash_value||'/'||b.PREV_HASH_VALUE SQL,
decode(b.status,'ACTIVE','A','INACTIVE','I','KILLED','K',STATUS) status,
last_call_et LT,
(sysdate -logon_time )*24*3600 LOGON_TIME
from v$session_wait a,v$session b
where ((a.event not like 'SQL*Net message%' and a.event not like 'rdbms%') or b.status='ACTIVE') and a.sid=b.sid
and b.type='USER'
and b.sid <>(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)
order by sql_hash_value;
We can get the final blocking in v$session by this query:
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
select distinct FINAL_BLOCKING_SESSION from v$session where FINAL_BLOCKING_SESSION_STATUS='VALID';
SQL> SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30)
FROM v$wait_chains;
Top 100 wait chain processes
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)||
' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'<none>',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
v$instance i
WHERE wc.instance = i.instance_number (+)
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101;
The final blocker is the session/process at the top of the wait chain. This is the session/process that maybe causing the problem. Example of query with final_blocking_session info:
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column fblocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)||
' from Instance '||blocker_instance BLOCKER_PROC,
'Number of waiters: '||num_waiters waiters,
'Final Blocking Process: '||decode(p.spid,null,'<none>',
p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC,
'Program: '||p.program image,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'<none>',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
gv$session s,
gv$session bs,
gv$instance i,
gv$process p
WHERE wc.instance = i.instance_number (+)
AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
and wc.sess_serial# = s.serial# (+))
AND (s.inst_id = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))
AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101;
