To check for Various Locking/Blocking Issues



Different Lock Modes
with explanation

0,
‘None’,
1,
‘Null’,
2,
‘Row-S (SS)’,
3,
‘Row-X (SX)’,
4,
‘Share’,
5,
‘S/Row-X (SSX)’,  
6,
‘Exclusive’

EXCLUSIVE
(X)                                       Lock
allows queries but nothing else.
SHARE
(S)                                               Lock
allows queries but not updates.
ROW
SHARE (RS)                                  Lock allows concurrent process access
to table. Resource may not be locked exclusively.
ROW
EXCLUSIVE (RX)                           Same as row share but no share mode locking.
Updates, deletes and inserts use this lock mode.
SHARE
ROW EXCLUSIVE (SRX)            Lock used to do selective updates and
to allow other processes to look at rows in table but not lock table in share
mode or to update any rows. (Never used).


Indentifying blocking
session tree
@$ORACLE_HOME/rdbms/admin/catblock.sql

@$ORACLE_HOME/rdbms/admin/utllockt.sql

From v$lock
select  *  from
v$lock  where block>0;

select
* from v$lock where request>0;

From gv$lock global view
select
* from gv$lock where block>0;

select
* from gv$lock where request>0;

From dba_locks
select
distinct BLOCKING_OTHERS from dba_locks;

To find Session information

col
program for a15F
col
machine for a15
col
terminal for a15
set
lines 152

select
s.sid,
s.serial#,
‘*’||s.process||’*’  Client,
p.spid
Server,
s.sql_address,
s.sql_hash_value,
s.username,
s.action,
s.program
|| s.module,
s.terminal,
s.machine,
s.status,
–s.last_call_et
s.last_call_et/3600
from
gv$session s, gv$process p
where
p.addr=s.paddr and
s.sid=nvl(‘&sid’,s.sid)
and
p.spid=nvl(‘&spid’,p.spid)
and
nvl(s.process,-1)
= nvl(‘&ClientPid’,nvl(s.process,-1));


select
p.username pu,s.username su,s.status stat,s.sid ssid,s.serial#
sser,lpad(p.spid,7) pid,
s.process
client_process,s.machine client_machine from v$process p, v$session s
where    p.addr=s.paddr  and s.username is not null
and      s.sid=&BSID;

Blockers-Waiters Info
col
SESS for a12
set
lines 132
SELECT
DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,
id1,
id2, lmode, request, type
FROM
V$LOCK
WHERE
(id1, id2, type) IN
(SELECT
id1, id2, type FROM V$LOCK WHERE request>0)
ORDER
BY id1, request;
select
distinct holding_session from dba_waiters where holding_session not in (select
waiting_session
from dba_waiters);

select
s.sid, s.serial#, s.action, s.module, s.status, s.last_call_et,s.logon_time
from
v$session s
where
s.sid in (select sid from v$lock where block >0)
order
by sid;

Blocking session

SELECT
blocking_sid, num_blocked
FROM
( SELECT blocking_sid, SUM(num_blocked) num_blocked
FROM
( SELECT l.id1, l.id2,
MAX(DECODE(l.block,
1, i.instance_name||’-‘||l.sid,
2,
i.instance_name||’-‘||l.sid, 0 )) blocking_sid,
SUM(DECODE(l.request,
0, 0, 1 )) num_blocked
FROM
gv$lock l, gv$instance i
WHERE
( l.block!= 0 OR l.request > 0 ) AND
l.inst_id
= i.inst_id
GROUP
BY l.id1, l.id2)
GROUP
BY blocking_sid
ORDER
BY num_blocked DESC)
WHERE
num_blocked != 0

Blocker-waiter

set
linesize 80
col
sess for a15
SELECT
DECODE(request,0,’Holder: ‘,’Waiter: ‘)||trim(sid) sess,
id1,
id2, lmode, request, type
FROM
V$LOCK
WHERE
(id1, id2, type) IN
(SELECT
id1, id2, type FROM V$LOCK WHERE request>0)
ORDER
BY id1, request;

SELECT
DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,inst_id,
id1,
id2, lmode, request, type
FROM
GV$LOCK
WHERE
(id1, id2, type) IN
(SELECT
id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER
BY id1, request;

Blocking
sessions in RAC
set
linesize 999
select
INST_ID,
SID, SERIAL#, MODULE, ACTION
from
gv$session
where
SID
in (select sid from gv$lock where block=1);

select
* from gv$lock where (ID1,ID2) in
(select
ID1,ID2 from gv$lock where request>0);

select
DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,id1, id2, lmode, request,
type,inst_id
FROM gV$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK
WHERE
request>0)
ORDER BY id1, request;

select
* from GV_$GLOBAL_BLOCKED_LOCKS;

select
INST_ID,
SID, SERIAL#, MODULE, ACTION, status
from
gv$session
where
(SID,
inst_id)  in (select sid, inst_id  from gv$lock where block=1);
Recent Posts