实验:
模拟Library Cache Pin等待
select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
以OCP用户登录,创建下面两个存储过程
create or replace procedure pining
as
begin
null ;
end ;
/
create or replace procedure calling
as
begin
pining;
dbms_lock.sleep( 1000 );
end ;
/
然后运行calling过程
然后再开一个会话,重新编译pining过程
再看一个会话查看Library Cache Pin等待情况,为了更好的格式,我选择了pl/sql developer登录
select sid ,
seq#,
event,
p1,
p1raw,
to_number(p1raw, 'xxxxxxxxxxxxxxxxx'),
p2,
p2raw,
to_number(p2raw, 'xxxxxxxxxxxxxxxxx'),
p3,
p3raw,
to_number(p3raw, 'xxxxxxxxxxxxxxxxx')
state
from v$session_wait
where event like 'library%' ;
P1列是Library Cache Handle Address,Pn是十进制,PnRAW是相应的16进制
Library Cache Pin等待的对象的handle地址为P1RAW:0000000070971648
通过X$KGLPN,关联到具体是哪个session
X$KGLPN 的含义是[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
select a.sid,
a.username,
a.program,
b.addr,
b.KGLPNADR,
b.KGLPNUSE,
b.KGLPNSES,
b.KGLPNHDL,
b.kGLPNLCK,
b.KGLPNMOD,
b.KGLPNREQ
from v$session a, x$kglpn b
where a.saddr = b.kglpnuse
and b.kglpnhdl = '0000000070971648'
and b.KGLPNMOD <> 0;
select * from v$session_wait where sid = 45 ;
定位到具体的sql:
查看是什么sql导致了Library Cache Pin等待
select t.SQL_ADDRESS, t.SQL_HASH_VALUE, t.sql_id,t2.SQL_FULLTEXT,t2.SQL_TEXT
from v$session t,v$sql t2
where t.sid = 45
and t.SQL_ID = t2.SQL_ID;
阅读(605) | 评论(0) | 转发(0) |