library cache pin/lock的解決辦法
Oracle使用兩種資料結構來進行shared pool的併發控制:lock 和 pin.
Lock比pin具有更高的級別.
Lock在handle上獲得,在pin一個物件之前,必須首先獲得該handle的鎖定.
鎖定主要有三種模式: Null,share,Exclusive.
在讀取訪問物件時,通常需要獲取Null(空)模式以及share(共享)模式的鎖定.
在修改物件時,需要獲得Exclusive(排他)鎖定.
在鎖定了Library Cache物件以後,一個程式在訪問之前必須pin該物件.
同樣pin有三種模式,Null,shared和exclusive.
只讀模式時獲得共享pin,修改模式獲得排他pin.
通常我們訪問、執行過程、Package時獲得的都是共享pin,如果排他pin被持有,那麼資料庫此時就要產生等待.
在很多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都是我們關心的.接下來我們就研究一下這幾個等待事件.
(一).LIBRARY CACHE PIN等待事件
Oracle文件上這樣介紹這個等待事件:
"library cache pin" 是用來管理library cache的併發訪問的,pin一個object會引起相應的heap被
載入記憶體中(如果此前沒有被載入),Pins可以在三個模式下獲得:NULL,SHARE,EXCLUSIVE,可以認為pin是一種特定
形式的鎖.
當Library Cache Pin等待事件出現時,通常說明該Pin被其他使用者已非相容模式持有.
"library cache pin"的等待時間為3秒鐘,其中有1秒鐘用於PMON後臺程式,即在取得pin之前最多等待3秒鐘,否則就超時.
"library cache pin"的引數如下,有用的主要是P1和P2:
P1 - KGL Handle address.
P2 - Pin address
P3 - Encoded Mode & Namespace
"LIBRARY CACHE PIN"通常是發生在編譯或重新編譯PL/SQL,VIEW,TYPES等object時.編譯通常都是顯性的,
如安裝應用程式,升級,安裝補丁程式等,另外,"ALTER","GRANT","REVOKE"等操作也會使object變得無效,
可以透過object的"LAST_DDL"觀察這些變化.
當object變得無效時,Oracle 會在第一次訪問此object時試圖去重新編譯它,如果此時其他session已經把此object pin
到library cache中,就會出現問題,特別時當有大量的活動session並且存在較複雜的dependence時.在某種情況下,重新
編譯object可能會花幾個小時時間,從而阻塞其它試圖去訪問此object的程式.
下面讓我們透過一個例子來模擬及解釋這個等待:
1.建立測試用儲存過程
[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.模擬
首先執行calling過程,在calling過程中呼叫pining過程
此時pining過程上獲得共享Pin,如果此時嘗試對pining進行授權或重新編譯,將產生Library Cache Pin等待
直到calling執行完畢.
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 |
此時calling開始執行
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; |
此時session 2掛起
ok,我們開始我們的研究:
從v$session_wait入手,我們可以得到哪些session正在經歷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秒就超時,seq#會發生變化
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
|
在這個輸出中,P1 列是Library Cache Handle Address,Pn欄位是10進製表示,PnRaw欄位是16進製表示
我們看到,library cache pin等待的物件的handle地址為:52D6730C
透過這個地址,我們查詢X$KGLOB檢視就可以得到物件的具體資訊:
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代表該物件的Hash Value
由此我們知道,在PINING物件上正經歷library cache pin的等待.
然後我們引入另外一個內部檢視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
|
透過聯合v$session,可以獲得當前持有該handle的使用者資訊.
對於我們的測試sid=13的使用者正持有該handle
那麼這個使用者正在等什麼呢?
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,這個使用者正在等待一次PL/SQL lock timer計時.
得到了sid,我們就可以透過v$session.SQL_HASH_VALUE,v$session.SQL_ADDRESS等欄位關聯v$sqltext,v$sqlarea等檢視獲得當前session正在執行的操作.
SQL> select sql_text from v$sqlarea where v$sqlarea.hash_value='3045375777';
SQL_TEXT
--------------------------------------------------------------------------------
BEGIN calling; END;
|
這裡我們得到這個使用者正在執行calling這個儲存過程,接下來的工作就應該去檢查calling在作什麼了.
我們這個calling作的工作是dbms_lock.sleep(3000)
也就是PL/SQL lock timer正在等待的原因
至此就找到了Library Cache Pin的原因.
簡化一下以上查詢:
1.獲得Library Cache Pin等待的物件
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.獲得持有等待物件的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.獲得持有物件使用者執行的程式碼
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之前和之後我們可以轉儲一下shared pool的內容觀察比較一下:
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模式鎖定,此模式允許其他使用者繼續以Null/shared模式鎖定該物件
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
|
在發出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執行未完成,在object上仍讓保持共享pin
--由於grant會導致重新編譯該物件,所以在handle上的排他鎖已經被持有
--進一步的需要獲得object上的Exclusive pin,由於shared pin被calling持有,所以library cache pin等待出現.
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 |
實際上recompile過程包含以下步驟,我們看一下lock和pin是如何交替發揮作用的:
1.儲存過程的library cache object以排他模式被鎖定,這個鎖定是在handle上獲得的
exclusive鎖定可以防止其他使用者執行同樣的操作,同時防止其他使用者建立新的引用此過程的物件.
2.以shared模式pin該物件,以執行安全和錯誤檢查.
3.共享pin被釋放,重新以排他模式pin該物件,執行重編譯.
4.使所有依賴該過程的物件失效
5.釋放exclusive lock和exclusive pin
(二).LIBRARY CACHE LOCK等待事件
如果此時我們再發出一條grant或compile的命令,那麼library cache lock等待事件將會出現:
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;
|
此程式掛起,我們查詢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已經被session 2以exclusive模式持有,所以session 3產生了等待.
我們可以看到,在生產資料庫中許可權的授予、物件的重新編譯都可能會導致library cache pin等待的出現.
所以應該儘量避免在高峰期進行以上操作.
另外我們測試的案例本身就說明:如果Package或過程中存在複雜的、互動的依賴以來關係極易導致library cache pin的出現.
所以在應用開發的過程中,我們也應該注意這方面的內容.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2139743/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 定位Library Cache pin,Library Cache lock等待的解決方法
- zt_如何平面解決library cache lock和library cache pin
- Library cache lock/pin詳解(轉)
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- library cache pin和library cache lock的診斷分析
- enq：Library cache lock/pin等待事件ENQ事件
- library cache lock和library cache pin區別總結
- 解決library cache pin等待事件事件
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- 分析解決因”library cache pin”等待
- library cache lock\pin的查詢與處理
- zt_library cache pin和lock等待分析
- Shared pool的library cache lock/pin及硬解析
- 0317Library Cache Pin/Lock Wait EventsAI
- 【ASM_ORACLE】Library Cache最佳化篇（二）Library cache load lock的概念和解決辦法ASMOracle
- zt_如何使用event 10049分析定位library cache lock and library cache pin
- Library cache pin/lock 在Oracle 10g的增強Oracle 10g
- 記一次library cache pin事件解決事件
- library cache lock和cursor: pin S wait on X等待AI
- 一次library cache pin故障的解決過程
- sql version count引發cursor:pin s wait x及library cache latch library cache lockSQLAI
- [Oracle]--Library cache lock 故障解決一例Oracle
- 由最長SQL想到的Latch Free( Library Cache Pin/Lock)整理~~草稿SQL
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle
- 【ASK_ORACLE】Library cache pin 與 library load lock的關係和區別Oracle
- 【等待事件】library cache pin事件
- 等待事件--library cache pin事件
- library cache pin等待分析
- library cache pin 等待事件事件
- 0106library cache pin的快速定位與解決
- 11G資料庫之library cache lock及library cache pin模擬結合hanganalyze定位資料庫
- 共享池之六:shared pool latch/ library cache latch /lock pin 簡介
- library cache pin等待事件的模擬事件
- LIBRARY CACHE LOCK 等待事件事件
- Oracle Edit product卡死不動，引起的等待事件‘library cache pin’解決方案Oracle事件
- library cache pin 阻塞程式查詢
- 查詢Library Cache Pin等待原因