关于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倍。

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')));
Trackback

no comment untill now

Add your comment now

切换到手机版