分析解決因”library cache pin”等待

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

#su - oracle

$svrmgrl

svrmgr>connect internal

svrmgr>select sid,event,p1,p2,p3 from 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

KGLM0 KGLMN KGLMS KGLMX

KGLM0 - X X X

KGLMN - - X X

KGLMS - - - X

KGLMX - ? X X

其中: KGLM0,KGLMN,KGLMS和KGLMX是X$KGLPN和X$KGLOB中的KGLHDLMD欄位的幾種狀態.

KGLM0 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 Handle Address

P2 列是Library Cache Pin Address.

(2). 把P1的值轉換為十六進位制,即15417016 --&gt EB3EB8,然後查詢X$KGLPN表

(Library Cache Object Pin),可找到相關session,其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 003ECD10 0 2

05B0CB88 2 00AB4468 000E56E0 000E56E0 00EB3EB8 004473A0 0 2

05B0CBE0 3 005E1848 000E7180 000E7180 00EB3EB8 00A30F60 0 2

05B0CC38 4 00B6FDD8 00126E20 00126E20 00EB3EB8 00DD6738 0 2

05B0CC90 5 0099F950 000BD370 000BD370 00EB3EB8 00E07F78 0 2

05B0CCE8 6 00AEB650 001194B0 001194B0 00EB3EB8 00DB6BC8 0 2

05B0CD40 7 00A5D818 000C0440 000C0440 00EB3EB8 0043A1A8 0 2

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

其中:

KGLPNHDL --- Library Cache Handle Address

KGLPNADR --- Library Cache Pin Address.

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

KGLPNREQ --- Pin 請求

KGNMODE --- Pin 鎖

從以上查詢結果可以看到,有一個session正佔著pin鎖(KGNMOD=3),而其它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 -4 3

其中:

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=;

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

方法2

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

sql>select sid Waiter,

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

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

from v$session_wait where wait_time=0 and event like 'library cache pin%';

(2). 透過查詢DBA_LOCK_INTERNAL和V$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_requested,1,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 KGLPNMOD <> 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.kglpnuse )

;

(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相關的引數

在OPS 8.1.5, 8.1.6和8.1.7.0中,一定要在initsid.ora檔案中設定

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

結束語

以上介紹了”library cache pin”的定義,產生原因及對資料庫效能的影響,給出了詳

細的分析步驟和解決方法,本文適合較有經驗的資料庫管理員閱讀.

本文中給出的SQL語句請先測試後才能使用.