library cache pin大部分都是因为编译存储过程造成的
查找造成问题的数据库对象(一般为存储过程)
SELECT * FROM v$session_wait WHERE event = 'library cache pin' ORDER BY p1raw; SELECT kglnaown "Owner", kglnaobj "Object" FROM x$kglob WHERE kglhdadr = 'p1raw';
一般是因为在编译的过程中,有大量的程式调用,每个程式发现失效的对象都要进行编译,获取锁,造成并发,一般CPU会彪高
SELECT DECODE (lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION', 42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 55, 'XML SCHEMA', 56, 'JAVA DATA', 57, 'SECURITY PROFILE', 59, 'RULE', 62, 'EVALUATION CONTEXT', 'UNDEFINED') object_type, lob.KGLNAOBJ object_name, pn.KGLPNMOD lock_mode_held, pn.KGLPNREQ lock_mode_requested, ses.sid, ses.serial#, ses.username FROM x$kglpn pn, v$session ses, x$kglob lob, v$session_wait vsw WHERE pn.KGLPNUSE = ses.saddr AND pn.KGLPNHDL = lob.KGLHDADR AND lob.kglhdadr = vsw.p1raw AND vsw.event = 'library cache pin' ORDER BY lock_mode_held DESC
解决方法:杀掉SESSION,降低争用,让能够编译过去。如果不行,就锁定账号,杀掉已经连进来的会话,即可。