关于library cache lock和library cache pin严重等待的问题

今天系统出现严重等待问题，表现如下：
1. 现象
SQL> select count(*) from v$session;

COUNT(*)
----------
379
SQL> select count(*) from v$session where status='ACTIVE';

COUNT(*)
----------
120
当前情况，较正常时候，total session有所减少，active session增加了9倍。

[@more@]

SQL> select * from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';
发现大量的library cache lock和library cache pin等待，最长等待session约为半小时。
此时CPU使用率和内存使用率呈趋势性下降。

2. 查看相关时间段内，DDL操作造成的INVALID对象：
select *
from dba_objects
where last_ddl_time > to_date('20080401 10:00:00', 'yyyymmdd hh24:mi:ss')
AND last_ddl_time <= to_date('20080401 11:30:00', 'yyyymmdd hh24:mi:ss')
and (object_type like '%PACK%' or object_type like 'FUNCTION' OR
object_type = 'PROCEDURE')
AND STATUS = 'INVALID'
order by last_ddl_time desc;

3. 查看具体产生library cache lock 的对象：
SELECT KGLNAOWN, KGLNAOBJ
FROM x$kglob
WHERE kglhdadr in
(select P1RAW from v$session_wait where event like 'library cache%');

结合步骤2和3，定位到产生问题的INVALID对象。

4. 查看明细session：
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;

5. 查看具体是那些用户做了这个操作导致 library cache lock
select sid, program, machine
from v$session
where paddr in
(SELECT s.paddr
FROM x$kglpn p, v$session s
WHERE p.kglpnuse = s.saddr(+)
AND p.kglpnmod <> 0
and kglpnhdl in
(select p1raw
from v$session_wait
where event in ('library cache pin', 'library cache lock',
'library cache load lock')));

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7319461/viewspace-1001765/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7319461/viewspace-1001765/