library cache pin和lock之类等待事件出现,一般是由于编译object时候会出现,在安装程序、升级、安装补丁程序等或者alter、grant、revoke等操作会导致object失效,在第一次访问到这些失效的object时,会尝试去编译,如果此时其他session已经把这个object pin到library cache中之后就会出现类似的等待事件
解决方法:
1.在v$session_wait视图中查询出持有的对象
Select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event=’library cache pin’;
Eg:p1raw的值是07000000451497E0
2.其中p1是handle address,根据p1raw的值结合X$kglob(kernel generic library cache manager object)可以查看该对象的具体信息:
col KGLNAOWN for a10
col KGLNAOBJ for a20
select addr,kglhdadr,kglhdpar,kglnaown,kglnaobj,kglnahsh,kglhdobj from x$kglob where KGLHDADR='07000000451497E0';
3.结合X$kglpn和v$session可以查询出等待的进程:
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=’07000000451497E0’ and b.kglpnmod<>0;
根据sid查询出后台进程
select paddr from v$session where sid=‘sid’;
select spid from v$process where addr='addr';