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 等待的原因。