Most blocking lock detection scripts fail to consider that processes waiting for, but not yet holding, a lock can block other processes that need a conflicting lock on the same resource. To resolve such problems, it is essential to consider the order of waiters.
This script shows all the locks that are held or wanted for each resource, together with the number of seconds since the lock was granted or requested respectively, in order. This script is intended to supplement other blocking lock detection scripts such as Oracle's utllockt.sql.
column resource format a20
column sid format a4 justify right
break on resource
select /*+ ordered */
l.type || '-' || l.id1 || '-' || l.id2 "RESOURCE",
nvl(b.name, lpad(to_char(l.sid), 4)) sid,
decode(
l.lmode,
1, ' N',
2, ' SS',
3, ' SX',
4, ' S',
5, ' SSX',
6, ' X'
) holding,
decode(
l.request,
1, ' N',
2, ' SS',
3, ' SX',
4, ' S',
5, ' SSX',
6, ' X'
) wanting,
l.ctime seconds
from
sys.v_$lock l,
sys.v_$session s,
sys.v_$bgprocess b
where
s.sid = l.sid and
b.paddr (+) = s.paddr
order by
l.type || '-' || l.id1 || '-' || l.id2,
sign(l.request),
l.ctime desc
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。