library cache pin 等待事件

今天发现数据库很多有很多等待事件：library cache pin

Oracle文档上这样介绍这个等待事件：library cache pin 是用来管理library cache的并发访问的，pin一个Object会引起相应的heap被载入内存中（如果此前没有被加载），pins可以在Null、Share、Exclusive这3个模式下获得，可以认为pin是一种特定形式的锁。

 

当library cache pin等待事件出现时，通常说明该pin被其他用户已非兼容模式持有。library cache pin的等待时间为3秒钟，其中有1秒钟用于PMON后台进程，即在取得pin之前最多等待3秒钟，否则就超时。library cache pin的参数有P1（KGL Handle Address）、P2（Pin Address）和P3（Encoded Mode & Namespace），常用的主要是P1和P2。

 

library cache pin通常是发生在编译或重新编译PL/SQL、VIEW、TYPES等Object时。编译通常是显性的，如安装应用程序、升级、安装补丁程序等，另外ALTER、GRANT、REVOKE等操作也会使Object变得无效，可以通过Object的LAST_DDL_TIME观察这些变化。

 

当Object变得无效时，Oracle会在第一次访问此Object时试图去重新编译它，如果此时其他session已经把此Object pin到library cache中，就会出现问题，特别是当大量的活动session并且存在较复杂的dependence时。在某种情况下，重新编译Object可能会花费几个小时，从而阻塞其他试图去访问此Object的过程。

 

尝试查找unvalid的object，没有找到，通过v$session_wait查看。

SQL> select sid,
        seq#,
        event,
        p1,
        p1raw,
        p2,
        p2raw,
        p3raw,
        wait_time wt,
        seconds_in_wait sw,
        state
   from v$session_wait
  where event like 'library%'
    and wait_time = 0;   2    3    4    5    6    7    8    9   10   11   12   13   14 

       SID       SEQ# EVENT                                                                    P1 P1RAW                    P2 P2RAW            P3RAW                    WT         SW STATE
---------- ---------- ---------------------------------------------------------------- ---------- ---------------- ---------- ---------------- ---------------- ---------- ---------- -------------------
       734         78 library cache pin                                                7699657536 00000001CAEF7340 1.1390E+10 00000002A6E19978 00000000000000C8          0        871 WAITING
       752         59 library cache pin                                                7699657536 00000001CAEF7340 1.1335E+10 00000002A39EC6D8 00000000000000C8          0        207 WAITING
       841         53 library cache pin                                                7699657536 00000001CAEF7340 1.1296E+10 00000002A1440130 00000000000000C8          0        538 WAITING

在这个输出中，P1列是Library Cache Handle Address，Pn字段是十进制表示，PnRaw字段是十六进制表示。可以看到，library cache pin等待的对象的handle地址为 00000001CAEF7340，通过这个地址，查询X$KGLOB（[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject）视图就可以得到对象的具体信息。

 

SQL> select addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
  from x$kglob
 where kglhdadr = '00000001CAEF7340';  2    3 

ADDR             KGLHDADR         KGLHDPAR         KGLNAOWN   KGLNAOBJ                                                                                               KGLNAHSH KGLHDOBJ
---------------- ---------------- ---------------- ---------- ---------------------------------------------------------------------------------------------------- ---------- ----------------
00002AAF9D5C1B30 00000001CAEF7340 00000002B330A120            select * from ( select row_.*, rownum rownum_ from (select person.personno,       person.electror 2632698414 00000002319C41B8
                                                              egno,       person.innercode,       person.name,       sex.codename as sex,       tagerSchoo

 

 

可以看到执行的语句是一个 SELECL 查询。这里KGLNAHSH代表该对象的Hash Value，由此知道，在PINING对象上正经历library cache pin的等待。然后引入另外一个内部视图 X$KGLPN（[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s）。

 

SQL> 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 = '00000001CAEF7340'
   and b.KGLPNMOD <> 0;  2    3    4    5    6    7    8    9   10   11   12   13   14   15 

       SID USERNAME                       PROGRAM                                          ADDR             KGLPNADR         KGLPNUSE         KGLPNSES         KGLPNHDL         KGLPNLCK           KGLPNMOD   KGLPNREQ
---------- ------------------------------ ------------------------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ----------
       990 JSJW                           JDBC Thin Client                                 00002AAF9D5C5408 00000002A2395FE0 00000002AB33C330 00000002AB33C330 00000001CAEF7340 00                        3          0

通过联合v$session，可以获得当前持有该handle的用户信息，对于测试sid=990的用户正持有该handle。那么这个用户正在等什么呢？

 

SQL> select * from v$session_wait where sid = 990;

       SID       SEQ# EVENT                                                            P1TEXT                                                                   P1 P1RAW            P2TEXT                                                      P2 P2RAW
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ----------------
P3TEXT                                                                   P3 P3RAW            WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS                                                        WAIT_TIME SECONDS_IN_WAIT STATE
---------------------------------------------------------------- ---------- ---------------- ------------- ----------- ---------------------------------------------------------------- ---------- --------------- -------------------
       990       2262 SQL*Net message from client                                      driver id                                                        1952673792 0000000074637000 #bytes                                                       1 0000000000000001
                                                                          0 00                  2723168908           6 Idle                                                                     -1          103101 WAITED SHORT TIME

现在可以看到，这个会话正在等待一个SQL*Net message from client。

 

得到了sid，就可以通过v$session.SQL_HASH_VALUE、v$session.SQL_ADDRESS等字段关联v$sqltext、v$sqlarea等视图获得当前Session正在执行的操作。
select sql_text,sql_id
  from v$sqlarea
 where hash_value = (select SQL_HASH_VALUE from v$session where sid = 990);

 

如果上面的语句看不全整个SQL，可以采用下面的语句
select sql_text
  from v$sqltext_with_newlines
 where hash_value = (select SQL_HASH_VALUE from v$session where sid = 990)
 order by piece;

至此，找到了  library cache pin 等待的原因。

 

 

 

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

转载于:http://blog.itpub.net/9399028/viewspace-746233/