library cache pin和library cache lock (zt)
http://m77m78.itpub.net/post/125/287710
一、概述
一個例項中的library cache包括了不同型別物件的描述,如:遊標,索引,表,檢視,過程,等等.
這些物件不能在他們被使用的時候改變,他們在被使用的時候會被一種library locks and pins的機制鎖住.
一個會話中,需要使用一個物件,會在該物件上先得到一個library lock(null, shared or exclusive模式的)
這是為了,防止其他會話也訪問這個物件(例如:重編譯一個包或檢視的時候,會加上exclusive型別的鎖)或更改物件的定義.
總的來說,library cache pin和library cache lock都是用於share pool的併發控制的。pin和lock都可以看作是一種鎖。
locks/pins會在SQL語句執行期間一直保持,在結束的時候才釋放。
一、概述
一個例項中的library cache包括了不同型別物件的描述,如:遊標,索引,表,檢視,過程,等等.
這些物件不能在他們被使用的時候改變,他們在被使用的時候會被一種library locks and pins的機制鎖住.
一個會話中,需要使用一個物件,會在該物件上先得到一個library lock(null, shared or exclusive模式的)
這是為了,防止其他會話也訪問這個物件(例如:重編譯一個包或檢視的時候,會加上exclusive型別的鎖)或更改物件的定義.
總的來說,library cache pin和library cache lock都是用於share pool的併發控制的。pin和lock都可以看作是一種鎖。
locks/pins會在SQL語句執行期間一直保持,在結束的時候才釋放。
每個想使用或修改已經locked/pin的物件的SQL語句,將會等待事件'library cache pin'或'library cache lock'直到超時.
超時,通常發生在5分鐘後,然後SQL語句會出現ORA-4021的錯誤.如果發現死鎖,則會出現ORA-4020錯誤。
二、library cache pin和library cache lock成因
lock主要有三種模式: Null,share(2),Exclusive(3).
在讀取訪問物件時,通常需要獲取Null(空)模式以及share(共享)模式的鎖定.
在修改物件時,需要獲得Exclusive(排他)鎖定.
同樣pin有三種模式,Null,shared(2)和exclusive(3).
只讀模式時獲得共享pin,修改模式獲得排他pin.
模式為shared(2)的pin會阻塞任何exclusive(3)的pin請求。
模式為shared(3)的pin也會阻塞任何exclusive(2)的pin請求。
不同的操作會對物件請求不同的lock/pin
1、所有的DDL都會對被處理的物件請求排他型別的lock和pin
2、
當要對一個過程或者函式進行編譯時,需要在library cache中pin該物件。在pin該物件以前,需要獲得該物件handle的鎖定,如果獲取失敗,就會產生library cache lock等待。如果成功獲取handle的lock,則繼續在library cache中pin該物件,如果pin物件失敗,則會產生library cache pin等待。
如果是儲存過程或者函式,可以這樣認為:如果存在library cache lock等待,則一定存在library cache pin等待;反過來,如果存在library cache pin等待,不一定會存在library cache lock等待;
但如果是表引起的,則一般只有library cache lock等待,則不一定存在library cache pin。
可能發生library cache pin和library cache lock的情況:
1、在儲存過程或者函式正在執行時被編譯。
2、在儲存過程或者函式正在執行時被對它們進行授權、或者移除許可權等操作。
3、對某個表執行DDL期間,有另外的會話對該表執行DML或者DDL。
4、PL/SQL物件之間存在複雜的依賴性
每個想使用或修改已經locked/pin的物件的SQL語句,將會等待事件'library cache pin'或'library cache lock'直到超時.
超時,通常發生在5分鐘後,然後SQL語句會出現ORA-4021的錯誤.如果發現死鎖,則會出現ORA-4020錯誤。
例如:
SES1:
執行:exec p_sleep;
假設儲存過程p正在執行,且執行時間很長
SES2:
執行:grant execute on p_sleep to system
對p進行編譯,如果之前沒有其他會話lock儲存過程p的handle,則本會話會將獲取p的handle鎖定;但會話pin p時會失敗,此時在SES2上產生library cache pin等待。如果超過5分鐘仍然不能完成pin p,則會報錯:
ORA-04021: 等待鎖定物件 SUK.P_SLEEP 時發生超時。此時,本會話會釋放p的handle lock。(也可能是ORA-04020錯誤)
SES3:
執行:grant execute on p_sleep to system
在這個會話中繼續編譯p,則該會話在獲取p的handle鎖定時會失敗,在本會話產生library cache lock等待。如果SES2超時,則本會話會獲取p的handle lock,v$session_wait上的等待事件也由library cache lock變成ibrary cache pin,直到超時。
library cache pin
查詢v$session_wait檢視中library cache pin對應的P1、P2、P3
P1 = Handle address
這個就是引起library cache pin等待的物件被pin到library cache中的handle。一般用P1RAW(十六進位制)代替p1(十進位制)
可以用以下sql查詢那個使用者下的那個物件正在被請求pin:
SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob
WHERE kglhdadr='&P1RAW'
;
返回的OBJECT可能是具體的物件,也可能是一段SQL。
P2 = Pin address
自身的pin地址。一般用P2RAW(十六進位制)代替P2(十進位制)
P3 = Encoded Mode & Namespace
在library cache pin和library cache lock(一)我們簡單介紹了介紹library cache pin和library cache lock的成因,下面介紹如何解決library cache pin和library cache lock等待。
三、解決方法
有兩種方法可以查詢library cache pin的相關資訊,推薦使用第二種。
使用這種方法前,有必要先了解以下表或檢視:x$kglob、x$kgllk、x$kglpn、DBA_KGLLOCK
1) x$kglob
該基表主要是library cache object的相關資訊。
X$KGLOB--[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject
引用該基表的檢視有﹕GV$ACCESS、GV$OBJECT_DEPENDENCY、GV$DB_OBJECT_CACHE、GV$DB_PIPES、DBA_LOCK_INTERNAL﹑DBA_DDL_LOCKS
2) x$kgllk
該基表儲存了library cache中物件的鎖資訊,主要用於解決library cache lock。
其名稱含義是:
[K]ernel Layer
[G]eneric Layer
[L]ibrary Cache Manager ( defined and mapped from kqlf )
Object Locks
X$KGLLK - Object [L]oc[K]s
引用該基表的檢視有﹕
DBA_DDL_LOCKS ﹑DBA_KGLLOCK ﹑GV$ACCESS ﹑GV$OPEN_CURSOR
SQL> desc x$kgllk;
名稱 型別
---------- -----------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KGLLKADR RAW(4)
KGLLKUSE RAW(4) ---會話地址(對應v$session的saddr)
KGLLKSES RAW(4) ---owner地址
KGLLKSNM NUMBER ---SID
KGLLKHDL RAW(4) ---控制程式碼
KGLLKPNC RAW(4) ---the address of the call pin
KGLLKPNS RAW(4) ---對應跟蹤檔案中的session pin值
KGLLKCNT NUMBER
KGLLKMOD NUMBER ---持有鎖的模式(0為no lock/pin held﹐1為null,2為share﹐3為exclusive)
KGLLKREQ NUMBER ---請求鎖的模式(0為no lock/pin held﹐1為null,2為share﹐3為exclusive)
KGLLKFLG NUMBER ---cursor的狀態﹐8(10g前)或2048(10g)表示這個sql正在執行﹐
KGLLKSPN NUMBER ---對應跟蹤檔案的savepoint的值
KGLLKHTB RAW(4)
KGLNAHSH NUMBER ---sql的hash值(對應v$session的sql_hash_value)
KGLLKSQLID VARCHAR2(13) ---sql ID,sql識別符號
KGLHDPAR RAW(4) ---sql地址(對應v$session的sql_address)
KGLHDNSP NUMBER
USER_NAME VARCHAR2(30) ---會話的使用者名稱
KGLNAOBJ VARCHAR2(60) ---物件名稱或者已分析並開啟cursor的sql的前60個字元
3) x$kglpn
X$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
它是與x$kgllk相對應的表﹐是關於pin的相關資訊。它主要用於解決library cache pin
引用該表的檢視有﹕
DBA_KGLLOCK
SQL> desc x$kglpn;
名稱 型別
------------ ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KGLPNADR RAW(4)
KGLPNUSE RAW(4) ---會話地址(對應v$session的saddr)
KGLPNSES RAW(4) ---owner地址
KGLPNHDL RAW(4) ---控制程式碼
KGLPNLCK RAW(4)
KGLPNCNT NUMBER
KGLPNMOD NUMBER ---持有pin的模式(0為no lock/pin held﹐1為null,2為share﹐3為exclusive)
KGLPNREQ NUMBER ---請求pin的模式(0為no lock/pin held﹐1為null,2為share﹐3為exclusive)
KGLPNDMK NUMBER
KGLPNSPN NUMBER ---對應跟蹤檔案的savepoint的值
4) DBA_KGLLOCK
DBA_KGLLOCK是一個檢視,它聯合了x$kgllk和x$kglpn的部分資訊。
通過查詢,我們可以知道DBA_KGLLOCK檢視的構建語句:
SQL> SELECT * FROM DBA_VIEWS WHERE VIEW_NAME='DBA_KGLLOCK';
select kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype from x$kgllk
union all
select kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin' kgllktype from x$kglpn
瞭解了用到的幾個主要檢視或表的結構,我們就可以寫出編寫查詢來檢視相關資訊:
方法一、只能查詢library cache pin相關資訊
SQL> SELECT distinct decode(kglpnreq,0,'holding_session: '||s.sid,'waiting_session: '||s.sid) sid,
2 s.SERIAL#, kglpnmod "Pin Mode", kglpnreq "Req Pin",a.sql_text,kglnaown "Owner", kglnaobj "Object"
3 FROM x$kglpn p, v$session s,v$sqlarea a,v$session_wait sw,x$kglob x
4 WHERE p.kglpnuse=s.saddr
5 AND kglpnhdl=sw.p1raw
6 and kglhdadr=sw.p1raw
7 and event like 'library cache%'
8 and (a.hash_value, a.address) IN (
9 select
10 DECODE (sql_hash_value,
11 0,
12 prev_hash_value,
13 sql_hash_value
14 ),
15 DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
16 from v$session s2
17 where s2.sid=s.sid
18 )
19 ;
SID SERIAL# Pin Mode Req Pin SQL_TEXT Owner Object
-------------------- ---------- ---------- ---------- ---------------------------------------- ------------ --------------------
blocked_sid: 16 195 0 3 grant execute on p_s SUK P_SLEEP
blocker_sid: 20 15 2 0 begin p_sleep; end; SUK P_SLEEP
得到這個結果後,你可以根據實際情況kill掉阻塞的會話或者被阻塞的會話。
方法二、可以查詢library cache pin和library cache lock 的資訊
select Distinct /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
od.to_owner object_owner,
od.to_name object_name,
oc.Type,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested,
xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql
from dba_kgllock w, dba_kgllock h, v$session w1,
v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xh
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
And od.to_address = w.kgllkhdl
And od.to_name=oc.Name
And od.to_owner=oc.owner
And w1.sid=xw.KGLLKSNM
And h1.sid=xh.KGLLKSNM
And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)
And (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH)
;
WAITING_SESSION HOLDING_SESSION LOCK_OR_PIN OBJECT_OWNER OBJECT_NAME TYPE MODE_HELD MODE_REQUESTED WAIT_SQL HOLD_SQL
--------------- --------------- ----------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------- --------- -------------- ------------------------------------------------------------ ------------------------------------------------------------
18 19 Lock SUK P_SLEEP PROCEDURE Exclusive Exclusive grant execute on p_sleep to system grant execute on p_sleep to system
19 12 Pin SUK P_SLEEP PROCEDURE Share Exclusive grant execute on p_sleep to system begin p_sleep; end;
23 25 Lock SUK P_SLEEP2 PROCEDURE Exclusive Exclusive grant execute on p_sleep2 to system grant execute on p_sleep2 to system
25 14 Pin SUK P_SLEEP2 PROCEDURE Share Exclusive grant execute on p_sleep2 to system begin p_sleep2; end;
為了避免這種情況,可以在編譯過程或函式等物件時,先檢視一下是否有會話正在使用該物件,查詢語句如下:
SELECT Distinct sid using_sid,
s.SERIAL#, kglpnmod "Pin Mode", kglpnreq "Req Pin",kglnaown "Owner", kglnaobj "using_Object"
FROM x$kglpn p, v$session s,x$kglob x
WHERE p.kglpnuse=s.saddr
AND kglpnhdl=kglhdadr
And p.KGLPNUSE = s.saddr
And kglpnreq=0
And upper(kglnaobj) = upper('&obj');
如果有結果返回,則等待這些對話的操作執行完畢再重新編譯,也可以把這些會話kill。
四、其他解決方法
上面主要是用到SQL查詢相關資訊的解決方法,也可以使用10046、trace等方法實現。至於用那種方法好,見仁見智。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/756652/viewspace-474601/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- library cache pin和library cache lock(一)
- library cache pin和library cache lock(二)
- Library Cache最佳化篇(一)降低library cache lock和library cache pin的方法
- [20240920]跟蹤library cache lock library cache pin使用gdb.txt
- [20240824]跟蹤library cache lock library cache pin使用gdb.txt
- library cache lock和library cache bin實驗_2.0
- library cache pin(轉)
- [20241108]跟蹤library cache lock library cache pin使用gdb(11g)4.txt
- [20241108]跟蹤library cache lock library cache pin使用gdb(11g)3.txt
- [20241105]跟蹤library cache lock library cache pin使用gdb(11g)2.txt
- 【等待事件】library cache pin事件
- 【ASK_ORACLE】Library cache pin 與 library load lock的關係和區別Oracle
- Library Cache 診斷:Lock, Pin 以及 Load Lock (文件 ID 1548524.1)
- latch:library cache lock等待事件事件
- 【ASK_ORACLE】Library Cache概念篇(二)之Library Cache Pin的定義Oracle
- DBA手記(學習)-library cache pin
- Oracle Library cacheOracle
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- 一次library cache lock 問題分析
- 【ASM_ORACLE】Library Cache最佳化篇(二)Library cache load lock的概念和解決辦法ASMOracle
- [20210507]dump library_cache.txt
- [20190402]Library Cache mutex.txtMutex
- [20210507]分析library cache轉儲.txt
- 徹底搞清楚library cache lock的成因和解決方法(轉)
- [20210507]dump library_cache 2.txt
- [20240827]分析為什麼出現library cache lock等待事件2.txt事件
- [20240828]分析為什麼出現library cache lock等待事件5.txt事件
- Oracle 11g 密碼延遲認證與 library cache lock 等待Oracle密碼
- [20210524]分析library cache轉儲 3.txt
- [20210524]分析library cache轉儲 4.txt
- [20210508]分析library cache轉儲 2.txt
- [20210602]分析library cache轉儲 5.txt
- 【TUNE_ORACLE】等待事件之“library cache pins”Oracle事件
- [20201203]探究library cache mutex X 3.txtMutex
- 故障:核心表業務高峰期授權導致library cache lock和mutex x競爭Mutex
- [20220301]oracle如何定位使用library cache mutex.txtOracleMutex
- [20210902]library_cache物件級別轉儲.txt物件
- [20220302]oracle如何定位使用library cache mutex 2.txtOracleMutex