ITPub博客

首页 > 数据库 > Oracle > library cache pin&lock的相关的诊断视图:

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

原创 Oracle 作者:dbs101 时间:2012-06-23 23:44:17 0 删除 编辑
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博客 ” ,链接:http://blog.itpub.net/25105315/viewspace-733660/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 统计信息恢复
请登录后发表评论 登录
全部评论

注册时间:2010-12-18

  • 博文量
    92
  • 访问量
    426627