锁相关sql与视图

获取lock资源持有者
select * from gv$lock where block=1;
select * from dba_waiters;
 
获取latch相关持有者信息sql
select * from v$latchholder;
 
在高并发的环境中，争用往往发生在library cache handle和library cache object。
handle上的latch争用主要表现为library cache lock等待事件
select b.sid,a.user_name,a.kglnaobj
from x$kgllk a,v$session b
where a.kgllkhdl in
(select p1raw from v$session wait
where wait_time=0 and event='library cache lock')
and a.kgllkmod<>0
and b.saddr=a.kgllkuse;
 
LCO上的争用主要表现为library cache pin等待事件，可以用以下sql查出handle上的持有者信息：
select sid from x$kglpn,v$session
where kglpnhdl in
(select p1raw from v$session_wait
where wait_time=0 and event like 'library cache pin%')
and kglpnmod<>0
and v$session.saddr=x$kglpn.kglpnuse;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29076828/viewspace-1196284/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29076828/viewspace-1196284/