溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
library cache pin/lock的解決辦法是什么，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。
Oracle使用兩種數(shù)據(jù)結(jié)構(gòu)來(lái)進(jìn)行shared pool的并發(fā)控制:lock 和 pin.
Lock比pin具有更高的級(jí)別.
Lock在handle上獲得,在pin一個(gè)對(duì)象之前,必須首先獲得該handle的鎖定.
鎖定主要有三種模式: Null,share,Exclusive.
在讀取訪問(wèn)對(duì)象時(shí),通常需要獲取Null(空)模式以及share(共享)模式的鎖定.
在修改對(duì)象時(shí),需要獲得Exclusive(排他)鎖定.
在鎖定了Library Cache對(duì)象以后,一個(gè)進(jìn)程在訪問(wèn)之前必須pin該對(duì)象.
同樣pin有三種模式,Null,shared和exclusive.
只讀模式時(shí)獲得共享pin,修改模式獲得排他pin.
通常我們?cè)L問(wèn)、執(zhí)行過(guò)程、Package時(shí)獲得的都是共享pin,如果排他pin被持有,那么數(shù)據(jù)庫(kù)此時(shí)就要產(chǎn)生等待.
在很多statspack的report中,我們可能看到以下等待事件:
Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time -------------------------------------------- ------------ ------------ ------- library cache lock 75,884 1,409,500 48.44 latch free 34,297,906 1,205,636 41.43 library cache pin 563 142,491 4.90 db file scattered read 146,283 75,871 2.61 enqueue 2,211 13,003 .45 ------------------------------------------------------------- |
這里的library cache lock和library cache pin都是我們關(guān)心的.接下來(lái)我們就研究一下這幾個(gè)等待事件.
(一).LIBRARY CACHE PIN等待事件
Oracle文檔上這樣介紹這個(gè)等待事件:
"library cache pin" 是用來(lái)管理library cache的并發(fā)訪問(wèn)的,pin一個(gè)object會(huì)引起相應(yīng)的heap被
載入內(nèi)存中(如果此前沒(méi)有被加載),Pins可以在三個(gè)模式下獲得:NULL,SHARE,EXCLUSIVE,可以認(rèn)為pin是一種特定
形式的鎖.
當(dāng)Library Cache Pin等待事件出現(xiàn)時(shí),通常說(shuō)明該P(yáng)in被其他用戶已非兼容模式持有.
"library cache pin"的等待時(shí)間為3秒鐘,其中有1秒鐘用于PMON后臺(tái)進(jìn)程,即在取得pin之前最多等待3秒鐘,否則就超時(shí). "library cache pin"的參數(shù)如下,有用的主要是P1和P2: P1 - KGL Handle address. P2 - Pin address P3 - Encoded Mode & Namespace
"LIBRARY CACHE PIN"通常是發(fā)生在編譯或重新編譯PL/SQL,VIEW,TYPES等object時(shí).編譯通常都是顯性的,
如安裝應(yīng)用程序,升級(jí),安裝補(bǔ)丁程序等,另外,"ALTER","GRANT","REVOKE"等操作也會(huì)使object變得無(wú)效,
可以通過(guò)object的"LAST_DDL"觀察這些變化.
當(dāng)object變得無(wú)效時(shí),Oracle 會(huì)在第一次訪問(wèn)此object時(shí)試圖去重新編譯它,如果此時(shí)其他session已經(jīng)把此object pin
到library cache中,就會(huì)出現(xiàn)問(wèn)題,特別時(shí)當(dāng)有大量的活動(dòng)session并且存在較復(fù)雜的dependence時(shí).在某種情況下,重新
編譯object可能會(huì)花幾個(gè)小時(shí)時(shí)間,從而阻塞其它試圖去訪問(wèn)此object的進(jìn)程.
下面讓我們通過(guò)一個(gè)例子來(lái)模擬及解釋這個(gè)等待:
1.創(chuàng)建測(cè)試用存儲(chǔ)過(guò)程
[oracle@jumper udump]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 14:16:57 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 47256168 bytes Fixed Size 451176 bytes Variable Size 29360128 bytes Database Buffers 16777216 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> create or replace PROCEDURE pining 2 IS 3 BEGIN 4 NULL; 5 END; 6 / Procedure created. SQL> SQL> create or replace procedure calling 2 is 3 begin 4 pining; 5 dbms_lock.sleep(3000); 6 end; 7 / Procedure created. SQL> |
2.模擬
首先執(zhí)行calling過(guò)程,在calling過(guò)程中調(diào)用pining過(guò)程
此時(shí)pining過(guò)程上獲得共享Pin,如果此時(shí)嘗試對(duì)pining進(jìn)行授權(quán)或重新編譯,將產(chǎn)生Library Cache Pin等待
直到calling執(zhí)行完畢.
session 1:
[oracle@jumper oracle]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 16:13:43 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: SQL> exec calling |
此時(shí)calling開(kāi)始執(zhí)行
session 2:
[oracle@jumper udump]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 16:14:16 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: SQL> grant execute on pining to eygle; |
此時(shí)session 2掛起
ok,我們開(kāi)始我們的研究:
從v$session_wait入手,我們可以得到哪些session正在經(jīng)歷library cache pin的等待
SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state 2 from v$session_wait where event like 'library%'; SID SEQ# EVENT P1 P1RAW P2 P2RAW P3 WAIT_TIME SECONDS_IN_WAIT STATE ---- ---------- ------------------- ---------- -------- ---------- -------- ---------- ---------- --------------- ------- 8 268 library cache pin 1389785868 52D6730C 1387439312 52B2A4D0 301 0 2 WAITING 等待3秒就超時(shí),seq#會(huì)發(fā)生變化 SQL> SID SEQ# EVENT P1 P1RAW P2 P2RAW P3 WAIT_TIME SECONDS_IN_WAIT STATE ---- ---------- ------------------- ---------- -------- ---------- -------- ---------- ---------- --------------- ------- 8 269 library cache pin 1389785868 52D6730C 1387439312 52B2A4D0 301 0 2 WAITING SQL> SID SEQ# EVENT P1 P1RAW P2 P2RAW P3 WAIT_TIME SECONDS_IN_WAIT STATE ---- ---------- ------------------- ---------- -------- ---------- -------- ---------- ---------- --------------- -------- 8 270 library cache pin 1389785868 52D6730C 1387439312 52B2A4D0 301 0 0 WAITING |
在這個(gè)輸出中,P1 列是Library Cache Handle Address,Pn字段是10進(jìn)制表示,PnRaw字段是16進(jìn)制表示
我們看到,library cache pin等待的對(duì)象的handle地址為:52D6730C
通過(guò)這個(gè)地址,我們查詢X$KGLOB視圖就可以得到對(duì)象的具體信息:
Note: X$KGLOB--[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject
col KGLNAOWN for a10 col KGLNAOBJ for a20 select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ from X$KGLOB where KGLHDADR ='52D6730C' / ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ -------- -------- -------- ---------- -------------------- ---------- -------- 404F9FF0 52D6730C 52D6730C SYS PINING 2300250318 52D65BA4 |
這里KGLNAHSH代表該對(duì)象的Hash Value
由此我們知道,在PINING對(duì)象上正經(jīng)歷library cache pin的等待.
然后我們引入另外一個(gè)內(nèi)部視圖X$KGLPN:
Note:X$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
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 = '52D6730C' and b.KGLPNMOD<>0 / SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ ----- ----------- ---------------------------------------- -------- -------- -------- -------- -------- -------- ---------- ---------- 13 SYS sqlplus@jumper.hurray.com.cn (TNS V1-V3) 404FA034 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8 2 0 |
通過(guò)聯(lián)合v$session,可以獲得當(dāng)前持有該handle的用戶信息.
對(duì)于我們的測(cè)試sid=13的用戶正持有該handle
那么這個(gè)用戶正在等什么呢?
SQL> select * from v$session_wait where sid=13; SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE ---------- ---------- ------------------- --------- ---------- -------- ------- ---------- -------- ------- ---------- -------- ---------- --------------- ------- 13 25 PL/SQL lock timer duration 120000 0001D4C0 0 00 0 00 0 1200 WAITING |
Ok,這個(gè)用戶正在等待一次PL/SQL lock timer計(jì)時(shí).
得到了sid,我們就可以通過(guò)v$session.SQL_HASH_VALUE,v$session.SQL_ADDRESS等字段關(guān)聯(lián)v$sqltext,v$sqlarea等視圖獲得當(dāng)前session正在執(zhí)行的操作.
SQL> select sql_text from v$sqlarea where v$sqlarea.hash_value='3045375777'; SQL_TEXT -------------------------------------------------------------------------------- BEGIN calling; END; |
這里我們得到這個(gè)用戶正在執(zhí)行calling這個(gè)存儲(chǔ)過(guò)程,接下來(lái)的工作就應(yīng)該去檢查calling在作什么了.
我們這個(gè)calling作的工作是dbms_lock.sleep(3000)
也就是PL/SQL lock timer正在等待的原因
至此就找到了Library Cache Pin的原因.
簡(jiǎn)化一下以上查詢:
1.獲得Library Cache Pin等待的對(duì)象
SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj FROM x$kglob WHERE kglhdadr IN (SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%') / ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ -------- -------- -------- ---------- -------------------- ---------- -------- 404F2178 52D6730C 52D6730C SYS PINING 2300250318 52D65BA4 |
2.獲得持有等待對(duì)象的session信息
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.kglpnmod <> 0 AND b.kglpnhdl IN (SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%') / SQL> SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ ---------- ---------- ------------------------------------------------ -------- -------- -------- -------- -------- -------- ---------- ---------- 13 SYS sqlplus@jumper.hurray.com.cn (TNS V1-V3) 404F6CA4 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8 2 0 |
3.獲得持有對(duì)象用戶執(zhí)行的代碼
SELECT sql_text FROM v$sqlarea WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN ( SELECT sql_address, sql_hash_value FROM v$session WHERE SID IN ( SELECT SID FROM v$session a, x$kglpn b WHERE a.saddr = b.kglpnuse AND b.kglpnmod <> 0 AND b.kglpnhdl IN (SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%'))) / SQL_TEXT -------------------------------------------------------------------------------- BEGIN calling; END; |
在grant之前和之后我們可以轉(zhuǎn)儲(chǔ)一下shared pool的內(nèi)容觀察比較一下:
SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 32'; Session altered. |
在grant之前:
從前面的查詢獲得pining的Handle是52D6730C:
****************************************************** BUCKET 67790: LIBRARY OBJECT HANDLE: handle=52d6730c name=SYS.PINING hash=891b08ce timestamp=09-06-2004 16:43:51 namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000] kkkk-dddd-llll=0000-0011-0011 lock=N pin=S latch#=1--在Object上存在共享pin --在handle上存在Null模式鎖定,此模式允許其他用戶繼續(xù)以Null/shared模式鎖定該對(duì)象 lwt=0x52d67324[0x52d67324,0x52d67324] ltm=0x52d6732c[0x52d6732c,0x52d6732c] pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394[0x52d67394,0x52d67394] ref=0x52d67314[0x52d67314, 0x52d67314] lnd=0x52d673a0[0x52d67040,0x52d6afcc] LIBRARY OBJECT: bject=52d65ba4 type=PRCD flags=EXS/LOC[0005] pflags=NST [01] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change alloc(K) size(K) ----- -------- -------- ------ ---- ------ -------- -------- 0 52d65dac 52d65c90 I/P/A 0 NONE 0.30 0.55 4 52d65c40 52d67c08 I/P/A 1 NONE 0.44 0.48 |
在發(fā)出grant命令后:
****************************************************** BUCKET 67790: LIBRARY OBJECT HANDLE: handle=52d6730c name=SYS.PINING hash=891b08ce timestamp=09-06-2004 16:43:51 namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000] kkkk-dddd-llll=0000-0011-0011 lock=X pin=S latch#=1--由于calling執(zhí)行未完成,在object上仍讓保持共享pin --由于grant會(huì)導(dǎo)致重新編譯該對(duì)象,所以在handle上的排他鎖已經(jīng)被持有 --進(jìn)一步的需要獲得object上的Exclusive pin,由于shared pin被calling持有,所以library cache pin等待出現(xiàn). lwt=0x52d67324[0x52d67324,0x52d67324] ltm=0x52d6732c[0x52d6732c,0x52d6732c] pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394[0x52d67394,0x52d67394] ref=0x52d67314[0x52d67314, 0x52d67314] lnd=0x52d673a0[0x52d67040,0x52d6afcc] LIBRARY OBJECT: bject=52d65ba4 type=PRCD flags=EXS/LOC[0005] pflags=NST [01] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change alloc(K) size(K) ----- -------- -------- ------ ---- ------ -------- -------- 0 52d65dac 52d65c90 I/P/A 0 NONE 0.30 0.55 4 52d65c40 52d67c08 I/P/A 1 NONE 0.44 0.48 |
實(shí)際上recompile過(guò)程包含以下步驟,我們看一下lock和pin是如何交替發(fā)揮作用的:
1.存儲(chǔ)過(guò)程的library cache object以排他模式被鎖定,這個(gè)鎖定是在handle上獲得的
exclusive鎖定可以防止其他用戶執(zhí)行同樣的操作,同時(shí)防止其他用戶創(chuàng)建新的引用此過(guò)程的對(duì)象.
2.以shared模式pin該對(duì)象,以執(zhí)行安全和錯(cuò)誤檢查.
3.共享pin被釋放,重新以排他模式pin該對(duì)象,執(zhí)行重編譯.
4.使所有依賴該過(guò)程的對(duì)象失效
5.釋放exclusive lock和exclusive pin
(二).LIBRARY CACHE LOCK等待事件
如果此時(shí)我們?cè)侔l(fā)出一條grant或compile的命令,那么library cache lock等待事件將會(huì)出現(xiàn):
session 3:
[oracle@jumper oracle]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Tue Sep 7 17:05:25 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production SQL> alter procedure pining compile; |
此進(jìn)程掛起,我們查詢v$session_wait視圖可以獲得以下信息:
SQL> select * from v$session_wait; SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS STATE ---- ---- ------------------- --------------- ---------- -------- ------------ ---------- -------- ---------------- -------------- ---------- ------ --- 11 143 library cache pin handle address 1390239716 52DD5FE4 pin address 1387617456 52B55CB0 100*mode+namespace 301 0000012D 0 6 WAITING 13 18 library cache lock handle address 1390239716 52DD5FE4 lock address 1387433984 52B29000 100*mode+namespace 301 0000012D 0 3 WAITING 8 415 PL/SQL lock timer duration 120000 0001D4C0 0 00 0 00 0 63 WAITING .... 13 rows selected |
由于handle上的lock已經(jīng)被session 2以exclusive模式持有,所以session 3產(chǎn)生了等待.
我們可以看到,在生產(chǎn)數(shù)據(jù)庫(kù)中權(quán)限的授予、對(duì)象的重新編譯都可能會(huì)導(dǎo)致library cache pin等待的出現(xiàn).
所以應(yīng)該盡量避免在高峰期進(jìn)行以上操作.
另外我們測(cè)試的案例本身就說(shuō)明:如果Package或過(guò)程中存在復(fù)雜的、交互的依賴以來(lái)關(guān)系極易導(dǎo)致library cache pin的出現(xiàn).
所以在應(yīng)用開(kāi)發(fā)的過(guò)程中,我們也應(yīng)該注意這方面的內(nèi)容.
看完上述內(nèi)容，你們掌握l(shuí)ibrary cache pin/lock的解決辦法是什么的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
