library cache pin&lock的相关的诊断视图：

--dba_kgllock视图包含每个library pin和lock的记录。
DBA_KGLLOCK : one row for each lock or pin of the instance
-KGLLKUSE session address --会话的地址，v$session.saddr
-KGLLKHDL Pin/Lock handle --pin/lock的句柄
-KGLLKMOD/KGLLKREQ Holding/requested mode --持有和请求的模式
0           no lock/pin held
1           null mode
2           share mode
3           exclusive mode
-KGLLKTYPE Pin/Lock       --是pin或者lock
(created via the $ORACLE_HOME/rdbms/admin/catblock.sql)

--请求的会话
col requ_sql_text format a30
select /*+ ordered */ w1.sid waiting_session,
w.kgllktype lock_or_pin,
        w.kgllkhdl address,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
    'Unknown') mode_requested
        ,(select sql_text from v$sql sql where sql.hash_value = w1.sql_hash_value and rownum = 1)
          requ_sql_text
from dba_kgllock w, v$session w1
where (((w.kgllkmod = 0) or (w.kgllkmod= 1))     and
((w.kgllkreq != 0) and (w.kgllkreq != 1)))
and w.kgllkuse     =   w1.saddr
/

col requ_sql_text format a20
col held_sql_text format a20
select /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
        w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
    'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
    'Unknown') mode_requested
        ,(select sql_text from v$sql sql where sql.hash_value = w1.sql_hash_value and rownum = 1)
          requ_sql_text
        ,(select sql_text from v$sql sql where sql.hash_value = h1.sql_hash_value and rownum = 1)
          held_sql_text
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where (((h.kgllkmod != 0) and (h.kgllkmod != 1)     and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and (((w.kgllkmod = 0) or (w.kgllkmod= 1))     and
((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse     =   w1.saddr
and h.kgllkuse     =   h1.saddr
/

--查找请求的对象
select distinct kglnaobj from x$kgllk where kgllkuse in (select saddr from v$session where sid = 31);

--查找持有的对象
select distinct kglnaobj from x$kgllk where kgllkuse in (select saddr from v$session where sid = 29);

--查找请求的对象和持有的对象之间的关系
$ORACLE_HOME/rdbms/admin/utldtree.sql

exec deptree_fill('PROCEDURE', 'SYS', 'P1');

select * from ideptree;

--测试
create or replace procedure P1
is
begin
null;
dbms_lock.sleep(60);
end;
/

create or replace procedure P2
is
begin
P1;
end;
/

create or replace procedure P3
is
begin
P2;
end;
/

create or replace procedure P4
is
begin
P3;
end;
/

--session 1,运行p4
exec p4;

--session 2, 重新编译p1
alter procedure p1 compile;

--检查pin和lock的请求session和持有session
col requ_sql_text format a20
col held_sql_text format a20
select /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
        w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
    'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
    'Unknown') mode_requested
        ,(select sql_text from v$sql sql where sql.hash_value = w1.sql_hash_value and rownum = 1)
          requ_sql_text
        ,(select sql_text from v$sql sql where sql.hash_value = h1.sql_hash_value and rownum = 1)
          held_sql_text
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where (((h.kgllkmod != 0) and (h.kgllkmod != 1)     and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and (((w.kgllkmod = 0) or (w.kgllkmod= 1))     and
((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse     =   w1.saddr
and h.kgllkuse     =   h1.saddr
/

返回结果：
WAITING_SESSION HOLDING_SESSION LOCK ADDRESS MODE_HELD MODE_REQU REQU_SQL_TEXT        HELD_SQL_TEXT
--------------- --------------- ---- -------- --------- --------- -------------------- --------------------
             15              12 Pin 66BE1F54 Share     Exclusive alter procedure p1 c BEGIN p4; END;
                                                                  ompile

--查找请求的对象
select distinct kglnaobj from x$kgllk where kgllkuse in (select saddr from v$session where sid = 15);

--查找持有的对象
select distinct kglnaobj from x$kgllk where kgllkuse in (select saddr from v$session where sid = 12);

--查找请求的对象和持有的对象之间的关系
exec deptree_fill('PROCEDURE', 'SYS', 'P1');

select * from ideptree;

SQL> exec deptree_fill('PROCEDURE', 'SYS', 'P1');

PL/SQL 过程已成功完成。

SQL> select * from ideptree;

DEPENDENCIES

PROCEDURE SYS.P1
   PROCEDURE SYS.P2
      PROCEDURE SYS.P3
         PROCEDURE SYS.P4

--从v$session_wait获取p1raw的pin的句柄
col KGLNAOWN for a10
col KGLNAOBJ for a20
select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
from X$KGLOB
where KGLHDADR ='66BE1F54'
/

--查找持有pin句柄的会话
select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,
b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ, a.sql_hash_value, sql.sql_text
from v$session a,x$kglpn b, v$sql sql
where a.saddr=b.kglpnuse
and b.kglpnhdl = '66BE1F54'
and b.KGLPNMOD<>0
and

sql.hash_value = a.sql_hash_value
/

参考metalink note

ITPub博客

library cache pin&lock的相关的诊断视图：

最新文章