今天打算复习一下library cache lock/pin等待事件,于是模拟了以下的实验。
1.在u1用户下创建测试需要的存储过程
create or replace procedure p
as
begin
dbms_lock.sleep(10000);
end;
/
2.在A会话中执行该存储过程
exec p;
3.在B会话中重新编译存储过程
alter procedure p;
4.查询library cache pin等待事件的相关会话
select saddr,sid,username,event,p1raw from v$session where event='library cache pin';
SADDR SID USERNAME EVENT P1RAW
-------- ---------- ---------- ------------------------------ --------
33B35BE4 151 U1 library cache pin 2D6CF718
5.查询持有library cache pin的会话以及pin住的对象
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req",kglnaown "Owner",kglnaobj "Object"
FROM x$kglpn p, v$session s,x$kglob o
WHERE p.kglpnuse=s.saddr
AND p.kglpnhdl=o.kglhdadr and p.kglpnhdl='2D6CF718';
SID Mode Req Owner Object
---------- ---------- ---------- ---------- ----------
151 0 3 U1 P1
143 2 0 U1 P1
从输出结果可以看到会话151被回话143阻塞,143会话以模式2 pin住对象p1。
6.在C会话中drop存储过程
drop procedure p;
7.查询查看library cache lock等待事件的相关会话
select saddr,sid,username,event,p1raw from v$session where event='library cache lock';
SADDR SID USERNAME EVENT P1RAW
-------- ---------- ---------- ------------------------------ --------
33B2422C 136 U1 library cache lock 2D6CF718
8.查询持有library cache lock的会话以及lock住的对象
select user_name,kglnaobj "Owner",kgllkses saddr,kgllkreq req,kgllkmod mod,kglnaobj object
from x$kgllk lock_a
where kgllkmod > 0
and exists (select lock_b.kgllkhdl from x$kgllk lock_b
where kgllkses = '33B2422C' /* blocked session */
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq > 0);
USER_NAME Owner SADDR REQ MOD OBJECT
---------- ---------- -------- ---------- ---------- ----------
U1 P1 33B35BE4 0 3 P1
U1 P1 33B2C5A4 0 1 P1
这里出现了两行结果,不过从mod列可以判断33B2C5A4这个会话持有的lock模式为1(如果没记错的话数字1表示null),所以正在阻塞136会话的是会话地址为33B35BE4的会话。
你也可以通过以下sql做进一步验证
select sid,saddr,event,q.sql_text from v$session s,v$sql q
where saddr in ('33B35BE4','33B2C5A4') and s.sql_id=q.sql_id;
SID SADDR EVENT SQL_TEXT
---------- -------- ------------------------------ ----------------------------------------
143 33B2C5A4 PL/SQL lock timer BEGIN p1; END;
151 33B35BE4 library cache pin alter procedure p1 compile
从输出结果发现会话地址为33B35BE4的会话正在编译p1,所以该会话持有的lock模式肯定会x,而会话136正是被它所阻塞。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20801486/viewspace-719143/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20801486/viewspace-719143/