create or replace procedure prc_test1
is
begin
loop
execute immediate 'select * from dual';
end loop;
end;
SESSION 1644 执行存储过程:
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
1644 0 1
SQL> exec prc_test1;
SESSION 1628 编译存储过程:
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
1628 0 1
SQL> alter procedure prc_test1 compile;
此时出现如下等待事件；
SQL> select sid,p1, P1RAW,p2,p3,event from v$session_wait where event like '%library cache%';
SID P1 P1RAW P2 P3 EVENT
---------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
1628 2172090560 00000000817778C0 2196671232 301 library cache pin
此时1628 在等待library cache pin
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req",p.kglpnhdl
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='&P1RAW'
SQL> SELECT s.sid, kglpnmod "Mode", kglpnreq "Req",p.kglpnhdl
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='00000000817778C0' 2 3 4 ;
SID Mode Req KGLPNHDL
---------- ---------- ---------- ----------------
1628 0 3 00000000817778C0
1644 2 0 00000000817778C0
SQL> desc x$kglpn;
名称 类型
------------ ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KGLPNADR RAW(4)
KGLPNUSE RAW(4) ---会话地址(对应v$session的saddr)
KGLPNSES RAW(4) ---owner地址
KGLPNHDL RAW(4) ---句柄
KGLPNLCK RAW(4)
KGLPNCNT NUMBER
KGLPNMOD NUMBER ---持有pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLPNREQ NUMBER ---请求pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLPNDMK NUMBER
KGLPNSPN NUMBER ---对应跟踪
此时1628在等待excusive模式的锁,而1644是持有share 模式的锁
SESSION 1627 删除存储过程:
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
1627 0 1
SQL> drop procedure prc_test1;
1.查询查看library cache lock等待事件的相关会话
SQL> select sid,saddr,p1, P1RAW,p2,p3,event from v$session where event like '%library cache%';
SID SADDR P1 P1RAW P2 P3 EVENT
---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
1627 00000000899161C8 2172090560 00000000817778C0 2197280080 301 library cache lock
1628 0000000089917730 2172090560 00000000817778C0 2196671096 301 library cache pin
2.查询持有library cache lock的会话以及lock住的对象
SQL> 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 = '00000000899161C8' /* blocked session */
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq > 0); 2 3 4 5 6 7
USER_NAME Owner SADDR REQ MOD OBJECT
------------------------------ ------------------------------------------------------------ ---------------- ---------- ---------- ------------------------------------------------------------
TEST PRC_TEST1 000000008992CDB0 0 1 PRC_TEST1
TEST PRC_TEST1 0000000089917730 0 3 PRC_TEST1
这里出现了两行结果，不过从mod列可以判断35FD6A24这个会话持有的lock模式为1(如果没记错的话数字1表示null)，所以正在阻塞25会话的是会话地址为0000000089917730的会话。
你也可以通过以下sql做进一步验证
SQL> select sid,saddr,event,q.sql_text from v$session s,v$sql q
where saddr in ('000000008992CDB0','0000000089917730') and s.sql_id=q.sql_id; 2
SID SADDR EVENT SQL_TEXT
---------- ---------------- ---------------------------------------------------------------- ------------------------------
1628 0000000089917730 library cache pin alter procedure prc_test1 compile
1644 000000008992CDB0 SQL*Net message from client select * from dual
从输出结果发现会话地址为0000000089917730 的会话正在编译prc_test1 ,所以该会话持有的lock模式肯定会x，而会话1627正是被它所阻塞。
