解決library cache pin等待事件

本人最後一個9i的客戶前兩天出了效能問題，一線生產資料庫，十萬火急，結果發現9i資料庫的一個節點連線數為1300左右，盡然有240個異常等待事件，絕大部分是library cache pin和library cache lock，翻出05年寫的一遍手記，感覺真的老，解決此問題，如下：

造成資料庫效能下降或掛起的原因很多，“library cache pin”等待是其中之一。當資料庫效能嚴重下降或掛起時，我們透過查詢v＄session_wait,發現大量的“library cache pin”等待,查詢的SQL語句如下:

#su_oracle

 

$svrmgrl

 

svrmgr>connect internal

 

svrmgr>select sid,event,p1,p2,p3from v$session_wait where wait_time=0;

 

SID	EVENT	P1	P2	P3
9	Library cache pin	15417016	10090832	20
154	Library cache pin	15417016	11224168	20
341	Library cache pin	15417016	11449936	20
349	Library cache pin	15417016	16489792	20
390	Library cache pin	15417016	11992536	20
160	Library cache pin	15417016	6166600	20
20	Library cache pin	15417016	10868760	20

因此,我們斷定資料庫效能下降或掛起是由“library cache pin”引起的.這種情況通常發生在大量使用資料庫儲存PL/SQL塊的併發應用程式中,而在測試環境中則很難再現. 

 

LIBRARY CACHE PIN等待事件

 

那麼什麼是“library cache pin”等待呢? “library cache pin”事件是用來管理library cache的併發訪問的,pin一個object會引起相應的heap被載入記憶體中,如果客戶端需要修改或檢測這個object它就必須在鎖住後取得一個pin.

“library cache pin”的等待時間為3秒鐘,其中有1秒鐘用於PMON後臺程式,即在取得pin之前最多等待3秒鐘,否則就超時.

“library cache pin”的引數如下,有用的主要是P1和P2:

 

 P1-KGL Handle address.

 

 P2-Pin address

 

 P3-10*Mode+Namespace

 

 其中,P1,P2可與x$kglpn和x$kglob表相關. x$kglpn和x$kglob是ORACLE資料庫的內部資料字典.

 

 等待一個pin意味著另有一個阻塞者（blocker），它處於更高階別或不相容狀態，我透過以下matrix來判斷是否相容，“X”表示不相容：

Blocker

Waiter

KGLMO  KGLMN  KGLMS   KGLMX

KGLMO-     X      X         X

KGLMN-     -       X         X

KGLMS-      -       -       X

KGLMX-     ？      X         X

其中：KGLMO，KGLMN，KGLMS和KGLMX是X$KGLPN和X$KGLHDLMD欄位的幾種狀態。

 

KGLMO   0   no lock/pin held

 

KGLMN   1   null　mode

 

KGLMS   2   share mode

 

KGLMX   3  exclusive mode

 

常見的原因及解決方法

 

“LIBRARY CACHE PIN”通常是發生在編譯或重新編譯PL/SQL，VIEW，TYPES等object時，編譯通常都是顯性的，如安裝應用程式，升級，安裝補丁程式等，但object的重新編譯也可能發生在object變得無效時。

我們在處理因“LIBRARY CACHE PIN”引起的效能變慢或掛起時，應檢查object無效方面的原因。當我們對object進行維護，如“ALTER”，“GRANT”，“REVOKE”時，就會使object變得無效，透過object的“LAST-DDL”屬性可檢視到這些變化。

當object變得無效時，Oracle會在第一次訪問此object時試圖去重新編譯它，如果此時其他session已經把此object pin 到library cache中，就會出現問題，特別是當有大量的活動session並且存在較複雜的dependence時，在某種情況下，重新編譯object可能會花幾個小時時間，從而阻塞其他試圖去訪問此object的程式，這種情況我們可以透過library cache dump level 10，查詢“ALTER…COMPILE”sql語句和帶有“lock=X”或“pin=X”的object或handles得知。在某些時候，可能會報錯，如“ORA-600[17285]”“ORA-4061”“ORA-4065”“ORA-6508”等。

綜上所述，我們在對PL/SQL儲存過程中經常引用到object進行修改，授權，收回授權時必須非常小心。實際上，解決這些問題大多要依靠應用程式的開發和維護，應用程式開發商應該考慮到某些方案的決策可能會給應用程式的伸縮性和效能帶來負面影響。

以下列出幾種可能產生“library cache pin”的情況及其避免方法：

1．  使用者許可權管理

當對使用者的許可權進行管理即進行“grant”“revoke”時，可能產生“library cache pin”。

建議的避免方法：透過角色來對終端使用者授權或收回授權，從而避免產生“library cache pin”

2．  高峰時的object管理

在系統執行高峰對資料庫object的管理可能產生“library cache pin”。

建議的避免方法：把對資料庫object的管理安排到負載相對較小的時候。

3．  在PL/SQL包中存在大量的互跨的依賴性（dependency）

建議的避免方法：儘可能按等級來排列它們的結構。

詳細分析步驟

下面給出兩種方法來分析“library cache pin”，目的是找出哪些session在等待資源，哪些session正佔著這些資源，而佔著資源的這些session又在做什麼，從而找到問題的根源並加以解決。

方法1

（1）。透過查詢V$SESSION-WAIT找出等待“library cache pin”的session,其SQL語句如下:sql>select sid,substr(event,1,30),p1,p2,p3 from v$session_wait where wait_time=0 and event like ‘library cache pin%’;

 SID            SUBSTR(EVENT,1,30)         P1            P2        P3

  9                 library cache pin       15417016      10090832      20

 154                library cache pin       15417016      11224168      20

 341                library cache pin       15417016      11449936      20

 349                library cache pin       15417016      16489792      20

 390                library cache pin       15417016      11992536      20

 160                library cache pin       15417016      6166600       20

 20                 library cache pin       15417016      10868760      20

其中:

P1列是 Library Cache  Adress

P2列是 Library Cache  Pin Adress.

(2).把P1的值轉換為十六進位制,即15417016- - >EB3EB8,然後查詢X$KGLPN表(Library Cache Object Pin),可找到相關ｓｅｓｓｉｏｎ，其SQL語句如下 (即把V$SESSION_WAIT中的P1與X$KGLPN中的KGLPNHDL相關連):

 

Sql>select ADDR,INDX,KGLPNADR,KGLPNUSE,KGLPNSES,KGLPNHDL,KGLPNLCK,KGLPNMOD,KGLPNREQ from x$kglpn where KGLPNHDL like’%EB3EB8%’;

 

ADDR         INDX         KGLPNADR        KGLPNUSE          KGLPNSES

KGLPNHDL   KGLPNLCK      KGNMOD        KGLPNREQ

 

05B0CB30        1            00FB9D40          0011B830             0011B830

00EB3EB8    033ECD10            0                 2

 

05B0CB30        2            00AB4468          000E56E0             000E56E0

00EB3EB8   004473A0            0                  2

 

05B0CB30        3            005E1848           000E7180             000E7180

00EB3EB8   00A30F60            0                  2

 

 

05B0CB30         4           00B6FDD8         00126E20              00126E20

00EB3EB8     00DD6738           0                 2

 

05B0CB30         5           0099FP50          000BD370             000BD370

00EB3EB8     00E07F78            0                 2

 

05B0CB30         6           00AEB650         001194B0              001194B0

00EB3EB8     00DB6BC8           0                 2

 

05B0CB30         7          00A5D818         000C0440            000C0440

00EB3EB8    0043A1A8            0                 2

 

05B0CB30         8           004001E8          00103A90           00103A90

00EB3EB8     004CA428            3                  0                        

 

其中:

KGLPNHDL  -----  Library Cache Handle Address

KGLPNADR  -----  Library Cache Pin Adress

KGLPNSES  ------  識別鎖住此pin 的session

KGLPNREQ  -----  Pin 請求

KGNMODE  ------  Pin 鎖

從以上查詢結果可以看到，有一個session 正佔著pin 鎖(ＫＧＮＭＯＤ＝３)，而其它session正等待此pin鎖(KGLPNREQ=2):

 

  ADDR INDX KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGNMOD KGLPNREQ

 

  05B0CD98  8  004001E8 00103A90 00103A90  00EB3EB8  004CA428    3     0

 

(3).查詢X$KGLOB(Library Cache Object),可找到相關的object,其SQL語句如下(即把V$SESSION_WAIT中的P1與X$KGLOB中的KGLHDADR相關連):

sql>select*from X$KGLOB where KGLHDADR like ‘%EB3EB8%’;

ADDR INDX KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNADLK KGLNAHSH  KGLNATIM  KGLNAPTM

  KGLHDNSP   KGLHDLMD  KGLHDPMD  KGLHDFLG  KGLHDOBJ  KGLHDLDC  KGLHDIVC  KGLHDEXC  KGLHDLKC

  KGLHDKMK  KGLHDDMK K GLHDAMK  KGLOBFLG  KGLOBSTA  KGLOBTYP  KGLOBCNU  KGLOBHS0  KGLOBHS1  KGLO

  BHS2  KGLOBHS3  KGLOBHS4  KGLOBHS5  KGLOBHS6  KGLOBHS7  KGLOBPC0  KGLOBPC6  KGLOBMSZ  KGLOBPSZ  KGLOBPUS  KGLOBCXF  KGLOBSOR   KGLOBPRS  KGLOBDSK  KGLOBBUF  KGLOBUID  KGLOBSID  KGLOBOCT

   05B0A230  30  00EB3EB  8   00EB3EB8

   select count(*) from fa_facturas @decmovi where fa _cuenta  à KGLNAOBJ

   = : 1

   1569934176 22-ENE-97 0 1 3 1342242

   816 00EB3C30 2 0 67014 9 0 1 

   0 1 1 0 65535 1470 0

   0 0 0 0 0 0 0

   0 0 0 0 0 0 0 0

  0 -4 -43

   其中：

   KGLNAOBJ ----相關object的名字（取前面80個字元）

   這樣，即可查出“library cache pin”是針對哪個object的（此處為“fa_facturas”）.

   (4). 把V$SESSION的SADDRL列與X$KGLPN的KGLPNUSE列相關連,並查詢V$SESSION_WAIT,即可查出佔著pin鎖的session目前正在做什麼,其SQL語句如下:

   sql>select a.sid,a.username,a.program from v$session a,x$kglpn b where

a.saddr=b.kglpnuse and b.kglpnhdl like ‘%EB3EB8%’ and b.kgnmod<>0;

sql>select event from v$session_wait where sid=<sid>；\

   透過以上查詢,可看到此session正等待“Client Message”,即此使用者可能已離開客戶終端,這樣透過把此session殺掉即可解決問題.

   方法2

   (1).透過查詢V$SESSION_WAIT 找到正等待“library cache pin”的session(即等待者),其SQL語句如下;

   sql>select sid Waiter,

   substr(rawtohex(P2),1,30)Handle,

   substr(rawtohex(P3),1,30)Pin_addr

   from v$session_wait where wait_time=0 and event like＇library cache pin％＇；

　（２）．透過查詢ＤＢＡ＿ＬＯＣＫ＿ＩＮＴＥＲＮＡＬ和Ｖ$SESSION_WAIT,可得到與“library cache pin”等待相關的object的名字,其SQL語句如下:

   sql>select to_char(SESSION_ID,’999’)sid,

   substr(LOCK_TYPE,1,30)TYPE,

   substr(lock_id1,1,23)Object_Name,

   substr(mode_held,1,4)HELD,substr(mode_requested1,4)REQ,

   lock_id2 Lock_addr

   from dba_lock_internal

   where

   mode_requested<>’None’

   and mode_requested<>mode_held

   and session_id in (select sid from v$session_wait where wait_time=0

   and event like ‘library cache pin%’);

   (3).查出“library cache pin”佔有者（即阻塞者）的session id,其SQL語句如下;

   sql>select sid Holder, KGLPNUSE Sesion, KGLPNMOD Held, KGLPNREQ Req

   from x$kglpn ,v$session

   where KGLPNHDL in (select p1raw from v$session_wait

   where wait_time=0 and event like ‘library cache pin%’)

   and KGLPNOD<>0

   and v$session,saddr=x$kglpn.kglpnuse;

   (4).查出“library cache pin”佔有者(阻塞者)正在等什麼?

   sql>select sid,substr(event,1,30),wait_time

   from v$session_wait

   where sid in (select sid from x$kglpn,v$session

   where KGLPNHDL in (select p1raw from v$session_wait

   where wait_time=0 and event like ‘library cache pin%’)

   and KGLPNMOD<>0

   and v$session.saddr=x$kglpn.kgpnuse)

   ;

   (5).查出阻塞者正執行的SQL語句:

   sql>select sid,sql_text

   from v$session,v$sqlarea

   where v$session.sql_address=v$sqlarea.address

   and sid=<阻塞者的sid>;

   這樣,就可找到“library cache pin”等待的根源,從而解決由此引起的效能問題

   另外,也可透過system dump 來分析“library cache pin”等待舉例如下:

   系統狀態:

   9 : waiting for ‘library cache pin’ [PIN:handle=EB3EB8]

   154: waiting for  ‘library cache pin’ [PIN:handle=EB3EB8]

   341: waiting for  ‘library cache pin’ [PIN:handle=EB3EB8]

   341: waiting for  ‘library cache pin’ [PIN:handle=EB3EB8]

   390: waiting for  ‘library cache pin’ [PIN:handle=EB3EB8]

   160: waiting for  ‘library cache pin’ [PIN:handle=EB3EB8]

   20 : waiting for  ‘library cache pin’ [PIN:handle=EB3EB8]

   阻塞者(Blockers):

   ~~~~~~~----------

   PIN:handle=EB3EB8 263:last wait for ‘client message’

   Object名字:

   ~~~~~~~~~

   PIN: handle=EB3EB8   CRSR: select count(*)from fa_facturas@decmovi

   Where fa_cuenta

   = : 1

   其他解決方法

   當重新編譯某個package時,應確認當前沒有session在使用此package中的object.當我們重新編譯某個package時,如果有session正在執行此package中的procedure或function,那麼編譯就會掛起.因為編譯或分析（parse）package 或procedure或 function或 view時，Oracle需要先取得“library cache lock”和“library cache pin”以保證在編譯或分析（parse）期間沒有session正使用此object-因為我們正修改此object的定義並需要刪除後用新的定義來重建此object.

  我們可以用以下procedure來檢查是否有session正在使用某個package,如果此procedure有結果返回，則此時不能編譯此package.

  sql>Connect SYS

  password:

  sql> set serveroutput on

  sql> create or replace procedure who_is _using (obj_name varchar2) is

  begin

  dbms _output.enable(1000000);

  for i in (SELECT distinct b.username,b.sid

  FROM SYS.x$kglpn a,v$session b, SYS.x$kglob c

  WHERE a.KGLPNUSE=b.saddr and upper(c.KGLNAOBJ)like upper(OBJ_NAME)and a. KGLPNHDL =c.KGLHDADR)loop dbms_output.put_line(‘(‘||to_char(i.sid)||’)’||i.username);

   end loop;

   end;

   sql>execute who_is_using(‘my_package%’);

  (14)-SCOTT  #即scott使用者正在執行my_package

  以下查詢可得到正使用此package的session是否正佔著“library cache pin”鎖或正等待“library cache pin”鎖：

  sql>SELECT a. KGLPNMOD, a.KGLPNREQ,b.username,c,KGLNAOBJ,

c.KGLOBTYP

   FROM

   x$kglpn a,

   v$session b,

   x$kglob c

   WHERE

   a. KGLPNUSE=b.saddr and

   upper(c.KGLNAOBJ) like upper(‘%my_package%’)

and

a.       KGLPNHDL =c.KGLHDADR;

與library cache pin 相關引數

   在OPS8.1.5，8.1.6和8.1.7.0中，一定要在initsid.ora檔案中設定

   _SQLEXEC_PRLGRESSION_COST=0以避免因session等待“library cache pin”而造成的資料庫效能嚴重下降或資料庫掛起。

  結束語

  以上介紹了“library cache pin”的定義，產生原因及對資料庫效能的影響，給出了詳細的分析步驟和解決方法，本文適合較有經驗的資料庫管理員閱讀，本文中給出的SQL語句請先測試後才能使用。