<pre name="code" class="sql">create or replace procedure prc_test1
is
begin
loop
execute immediate 'select * from dual';
end loop;
end;
########################################
SESSION 20 执行存储过程: #
########################################
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
20 0 0
SQL> exec prc_test1;
此时的等待事件:
SQL> select sid,blocking_session,p1,p1raw,p2,p3,event from v$session where event not like '%message%' and sid=20;
SID BLOCKING_SESSION P1 P1RAW P2 P3 EVENT
---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
20 537557208 00000000200A78D8 293 0 latch: shared pool
#########################################
SESSION 1137编译存储过程: #
#########################################
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
1137 0 0
SQL> alter procedure prc_test1 compile;
SQL> select sid,blocking_session,p1,p1raw,p2,p3,event from v$session where event not like '%message%' and sid in (20,1137);
SID BLOCKING_SESSION P1 P1RAW P2 P3 EVENT
---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
20 537557208 00000000200A78D8 293 0 latch: shared pool
1137 20 832829180 0000000031A3F6FC 831962136 3.2728E+14 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 like '%&P1RAW%'
SQL> SELECT s.sid, kglpnmod "Mode", kglpnreq "Req",p.kglpnhdl
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr 2 3 ;
SID Mode Req KGLPNHDL
---------- ---------- ---------- --------
1137 0 3 31A3F6FC
20 2 0 31A3F6FC
可以发现持有者为20
######################################
SESSION 22删除存储过程: #
######################################
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
22 0 0
SQL> drop procedure prc_test1;
SQL> select sid,blocking_session,p1,p1raw,p2,p3,event from v$session where event not like '%message%' and sid in (20,1137,22);
SID BLOCKING_SESSION P1 P1RAW P2 P3 EVENT
---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
20 537557208 00000000200A78D8 293 0 latch: shared pool
22 1137 832829180 0000000031A3F6FC 618590516 3.2728E+14 library cache lock
1137 20 832829180 0000000031A3F6FC 831962136 3.2728E+14 library cache pin
查看X$KGLLK表
The X$KGLLK table (accessibleonly as SYS/INTERNAL) contains all the library object locks (both held &requested) for all sessions and is more complete than the V$LOCK view
althoughthe column names don't always reveal their meaning.
--X$KGLLK 表只能被SYS/INTERNAL用户访问，其包含所有library object locks的信息（held和requested）。
--查看等待事件为librarycache lock的session 的session address (SADDR):
SQL> select sid,saddr from v$session where event='library cache lock';
SID SADDR
---------- --------
22 2F391B2C
--从x$kgllk查看具体的锁信息：
SQL> select kgllkhdl Handle, kgllkreq Request,kglnaobj Object
from x$kgllk
where kgllkses = '2F391B2C'
and kgllkreq > 0; 2 3 4
HANDLE REQUEST OBJECT
-------- ---------- ------------------------------------------------------------
31A3F6FC 3 PRC_TEST1
KGLLKREQ: This will show you the library cache lock requested by this session(KGLLKREQ > 0)
KGLNAOBJ:contains the first 80 characters of the name of the object.
KGLLKHDL:corresponds with the 'handle address' of the object
--然后根据KGLLKHDL从X$KGLLK查看KGLLKMOD > 0的session，其正在持有该锁：
SQL> select kgllkses saddr, kgllkhdl handle,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 = '2F391B2C' /* blocked session*/
and lock_a.kgllkhdl =lock_b.kgllkhdl
and kgllkreq > 0); 2 3 4 5 6 7 8
SADDR HANDLE MOD OBJECT
-------- -------- ---------- ------------------------------------------------------------
2F397004 31A3F6FC 1 PRC_TEST1
2E063BA8 31A3F6FC 3 PRC_TEST1
SQL> select sid,saddr from v$session where saddr in ('2F397004','2E063BA8','2F391B2C');
SID SADDR
---------- --------
20 2F397004
22 2F391B2C ----被堵塞 library cache lock
1137 2E063BA8 ----被堵塞 library cache pin
SQL> select * from dba_kgllock where KGLLKHDL in ( select KGLLKHDL from dba_kgllock where kgllkmod =3);
KGLLKUSE KGLLKHDL KGLLKMOD KGLLKREQ KGLL
-------- -------- ---------- ---------- ----
2F391B2C 31A3F6FC 0 3 Lock
2F397004 31A3F6FC 1 0 Lock
2E063BA8 31A3F6FC 3 0 Lock
2E063BA8 31A3F6FC 0 3 Pin
2F397004 31A3F6FC 2 0 Pin
SQL> select sid,serial#,blocking_session,p1,p1raw,p2,p3,event from v$session where event='library cache lock';
SID SERIAL# BLOCKING_SESSION P1 P1RAW P2 P3 EVENT
---------- ---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
22 121 1137 832829180 0000000031A3F6FC 618590516 3.2728E+14 library cache lock
SQL> select sid,serial#,blocking_session,p1,p1raw,p2,p3,event from v$session
2 where sid=1137;
SID SERIAL# BLOCKING_SESSION P1 P1RAW P2 P3 EVENT
---------- ---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
1137 504 20 832829180 0000000031A3F6FC 619961492 3.2728E+14 library cache pin
SQL> select sid,serial# ,saddr from v$session where saddr in (
select KGLLKUSE from dba_kgllock where KGLLKHDL in ( select KGLLKHDL from dba_kgllock where KGLLKREQ =3)) 2 ;
SID SERIAL# SADDR
---------- ---------- --------
20 236 2F397004
22 121 2F391B2C
1137 504 2E063BA8
查看所有持有者:
SQL> select sid, serial#, saddr
from v$session
where saddr in (select KGLLKUSE
from dba_kgllock
where KGLLKHDL in
(select KGLLKHDL from dba_kgllock where KGLLKREQ >0)
and kgllkmod > 0); 2 3 4 5 6 7
SID SERIAL# SADDR
---------- ---------- --------
20 236 2F397004
1137 504 2E063BA8