Library cache lock/pin詳解(轉)
一、概述
---本文是網路資料加metalink 等整理得來
一個例項中的library cache包括了不同型別物件的描述,如:遊標,索引,表,檢視,過程,等等. 這些物件不能在他們被使用的時候改變,他們在被使用的時候會被一種library locks and pins的機制鎖住. 一個會話中,需要使用一個物件,會在該物件上先得到一個library lock(null, shared or clusive模式的)這是為了,防止其他會話也訪問這個物件(例如:重編譯一個包或檢視的時候,會加上exclusive型別的鎖)或更改物件的定義.
總的來說，library cache pin和library cache lock都是用於share pool的併發控制的。pin和lock都可以看作是一種鎖。locks/pins會在SQL語句執行期間一直保持,在結束的時候才釋放。Lock鎖的等級比pin要高。
每個想使用或修改已經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、對檢視，過程，包的編譯都要請求排他的lock和pin
當要對一個過程或者函式進行編譯時，需要在library cache中pin該物件。在pin該物件以前，需要獲得該物件handle的鎖定，如果獲取失敗，就會產生library cache lock等待。如果成功獲取handle的lock，則繼續在library cache中pin該物件，如果pin物件失敗，則會產生library cache pin等待。
如果是儲存過程或者函式，可以這樣認為：如果存在librarycache 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_;
假設儲存過程p正在執行，且執行時間很長
SES2:
執行：grant execute on p_sleep to system
對p進行編譯，如果之前沒有其他會話lock儲存過程p的handle，則本會話會將獲取p的handle鎖定；但會話pin p時會失敗，此時在SES2上產生library cache pin等待。如果超過5分鐘仍然不能完成pinp，則會報錯：
ORA-04021: 等待鎖定物件 SUK.P_SLEEP 時發生超時。此時，本會話會釋放p的handlelock。(也可能是ORA-04020錯誤)
SES3:
執行：grant execute on p_sleepto system
在這個會話中繼續編譯p，則該會話在獲取p的handle鎖定時會失敗，在本會話產生librarycache lock等待。如果SES2超時，則本會話會獲取p的handlelock，v$session_wait上的等待事件也由librarycache lock變成ibrary cache pin，直到超時。
librarycache pin
查詢v$session_wait檢視中library cache pin對應的P1、P2、P3
P1 = Handle aress
這個就是引起library cache pin等待的物件被pin到library cache中的handle。一般用P1RAW(十六進位制)代替p1(十進位制)
可以用以下sql查詢那個使用者下的那個物件正在被請求pin：
SELECT kgaown "Owner", kglnaobj "Object"
FROM x$kglob
WHERE kglhdadr='&P1RAW'
;
返回的OBJECT可能是具體的物件，也可能是一段SQL。
P2 =Pin address
自身的pin地址。一般用P2RAW(十六進位制)代替P2(十進位制)
P3 = Eoded Mode & Namespace
在library cache pin和library cache lock(一)我們簡單介紹了介紹library cache pin和librarycache 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為nolock/pin held﹐1為null,2為share﹐3為exclusive)
KGLLKREQ NUMBER ---請求鎖的模式(0為nolock/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
瞭解了用到的幾個主要檢視或表的結構，我們就可以寫出編寫查詢來檢視相關資訊：
方法一、只能查詢librarycache pin相關資訊
SQL> SELECT distinct decode(kglpnreq,0,'holding_session:'||s.s,'waiting_session: '||s.sid) sid,
2 s.SERIAL#, kglpnmod "Pin Mode", kglpnreq "ReqPin",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
得到這個結果後，你可以根據實際情況掉阻塞的會話或者被阻塞的會話。
方法二、可以查詢library cache pin和library cache lock 的資訊
;
WAITING_SESSION HOLDING_SESSION LOCK_OR_PIN OBJECT_OWNER OBJECT_NAME TYPEMODE_HELD MODE_REQUESTED WAIT_SQL HOLD_SQL
--------------- --------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- --------------------------------------------------------------------------------------------------------------------------------------
18 19 Lock SUK P_SLEEP PROCEDURE Exclusive Exclusive grant execute on p_sleepto system grant execute on p_sleep to system
19 12 Pin SUK P_SLEEP PROCEDURE Share Exclusive grant execute on p_sleep tosystem begin p_sleep; end;
23 25 Lock SUK P_SLEEP2 PROCEDURE Exclusive Exclusive grant execute on p_sleep2to system grant execute on p_sleep2 to system
25 14 Pin SUK P_SLEEP2 PROCEDURE Share Exclusive grant execute on p_sleep2 tosystem 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、ace等方法實現。至於用那種方法好，見仁見智。
Howto Find Session is Holding a Particular Library Cache Lock [ID 122793.1]
Troubleshooting Detai
CommonSituations
- A DML operation that is hanging because the table which is acces is currently undergoing changes (ALTER TABLE). This may take quite a long depending on the size of the table and the type of the modifiion (e.g. ALTER TABLE x MODIFY (1 CHAR(200) on a table with thousands of records)
Inthis case, V$LOCK will show that the session doing the 'ALTER TABLE' with anexclusive DML enqueue lock on the table object (LMODE=6, TYPE=TM where ID1 isthe OBJECT_ID of the table). The waiting session however does not show up inV$LOCK yet so in an environment with a lot of concurrent sessions the V$LOCKinfoation will be infficient to track down the culprit blocking youroperation.
- The compilation of package will hang on Library Cache Lock and Library Cache Pin if any users are executing a procere/function defined in the same package.
Method1: Systeme Analysis
Systemstate event willcreate a trace containing detailed information on every Oracle process.This information includes all the resources held & requested by a specifirocess.
While an operation is hanging, open a new session and launch the followingstatement:
For Oracle 9.2.0.1 or higher:
$sqlplus '/ as sysdba'
oradebug mypid
oradebug unlimit
oradebug systemstate266
For older versions you can use the following syntax that is also possible inhigher versions.The level 266 is not available before 9.2.0.6
alter session setmax_dump_file_size=unlimited;
alter session set events'immediate trace name systemstate level 10'
Oracle will create a systemstate tracefile in your USER_DUMP_DESTdirectory.
Get the PID (ProcessID) of the 'hanging' session:
select pid from v$process where addr=
(select paddr fro$session where sid=
The systemstate dump contains a separate section with information for eachprocess.
Open the tracefile and do a search for "PROCESS
In the process section search for the wait event by doing a search on 'waitingfor'.
PROCESS20:
----------------------------------------
SO: 0x7d2bd820, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=20, calls cur/top: 0x7d3d62d0/0x7d3d85dc, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 109 0 4
last post received-location: kslpsr
last process to post me: 7d2b8d94 1 6
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by me: 7d2b8d94 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 0x7d2ed5dc
O/S info: user: oracle, term: pts/7, ospid: 19759
OSD pid info: Unix process pid: 19759, image: goblin.forgotten.realms (TNSV1-V3)
(session) sid: 141 trans: (nil), creator: 0x7d2bd820, flag: (41) USR/-BSY/-/-/-/-/-
DID: 0001-0014-00000668, short-term DID: 0000-0000-00000000
txn branch: (nil)
oct: 6, prv: 0, sql: 0x62d01c34, psql: 0x7c20f24c, user: 542/SCOTT
service name: SYS$USERS
O/S info: user: oracle, term: pts/7, ospid: 19758, machine:goblin.forgotten.realms
program: sqlplus@goblin.forgotten.realms (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'library cache lock' blocking sess=0x(nil) seq=36 wait_time=0seconds since wait started=11
handle address=62d064dc, lockaddress=79f88a68, 100*mode+namespace=c9
- Use the handle address to information on the object locked:
SO: 0x79f88a68, type: 53,owner: 0x7d3d62d0, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=79f88a68 handle=62d064dc request=S
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x79f88ab4[0x79e71e60,0x79e71e60] htb=0x79e71e60 ssga=0x79e716fc
user=7d3a13b8 session=7d3a13b8 count=0 flags=[0000] savepoint=0xce
LIBRARY OBJECT HANDLE: handle=62d064dc mtx=0x62d06590(0) p=0
name=SCOTT.EMPLOYEES
We see the library object lock is being requested in Shared mode (request=S)
Name of the the object is SCOTT.EMPLOYEES
- Use the 'handle address' to find the process that is holding the lock on your resource by doing a search on the address within the same tracefile.
PROCESS 18:
----------------------------------------
SO: 0x7d2bcca8, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=18, calls cur/top: 0x79f3ab84/0x7d3d5fc8, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 109 0 4
last post received-location: kslpsr
last process to post me: 7d2b8d94 1 6
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by me: 7d2b8d94 1 6
SO: 0x75fe8f7c, type: 53, owner: 0x7b751914, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=75fe8f7c handle=62d064dc mode=X
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x75fe8fc8[0x79f81790,0x79fc3ef8] htb=0x79f81790 ssga=0x79f8102c
user=7d3988d0 session=7d3988d0 count=1 flags=[0000] savepoint=0x146e
LIBRARY OBJECT HANDLE: handle=62d064dc mtx=0x62d06590(0) cdp=0
name=SCOTT.EMPLOYEES
From the output we can see that the Process 18 (pid) is holding anexclusive lock (mode=X) on the object we are trying to access. Using V$PROCESSand V$SESSION we can retrieve the sid, user, terminal, program,... for thisprocess.
The actual statement that was launched by this session is also listed in thetracefile (statements and other library cache objects are preceded by 'name=').
METHOD 2: EXAMINE THE X$KGLLK TABLE
TheX$KGLLK table (accessible only as SYS/INTERNAL) contains all the library objectlocks (both held & requested) for all sessions and is complete thanthe V$LOCK view although the column names don't always reveal their meaning.
You can examine the locks requested (and held) by the waiting session bying up the session address (SADDR) in V$SESSION and doing the followingselect:
select sid,saddr fromv$session where event= 'library cache lock';
SID SADDR
---------- --------
16 572ed244
selectkgllkhdl Handle,kgllkreq Request, kglnaobj Object
fromx$kgllk where kgllkses = '572ed244'
andkgllkreq > 0;
HANDLE REQUEST OBJECT
-------- ----------------------------------------------------------------------
62d064dc 2 EMPLOYEES
This will show you the library cache lock requested by this session (KGLLKREQ> 0) where KGLNAOBJ contains the first 80 characters of the name of theobject.The value in KGLLKHDL corresponds with the 'handle address' of theobject in Method 1 Systemstate Analysis as shown above.
If we now match the KGLLKHDL with the handles of other sessions inX$KGLLK that should give us the address of the blocking session.The sessionholding the lock will have KGLLKMOD > 0 as it is holding the lock.
select kgllksessaddr,kgllkhdl handle,kgllkmod mod,kglnaobj object
fromx$kgllk lock_a
wherekgllkmod > 0
andexists (select lock_b.kgllkhdl from x$kgllk lock_b
wherekgllkses = '572ed244' /* blocked session */
andlock_a.kgllkhdl = lock_b.kgllkhdl
andkgllkreq > 0);
SADDR HANDLE MOD
-------- ------------------
OBJECT
------------------------------------------------------------
572eac94 62d064dc 3
EMPLOYEES
If we look a bit further we can then again match KGLLKSES with SADDR inv$session to find further information on the blocking session:
selectsid,username,terminal,program from v$session where saddr = '572eac94'
SID USERNAME TERMINAL
---------------------------------------- ------------------------------
PROGRAM
------------------------------------------------
12 SCOTT pts/20
sqlplus@goblin.forgotten.realms(TNS V1-V3)
In the same way we can also find all the blocked sessions:
selectsid,username,terminal,program from v$session
wheresaddr in
(selectkgllkses from x$kgllk lock_a
wherekgllkreq > 0
andexists (select lock_b.kgllkhdl from x$kgllk lock_b
where kgllkses = '572eac94' /* blocking session */
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq = 0)
);
SID USERNAME TERMINAL
---------- ------------------------------------------------------------
PROGRAM
------------------------------------------------
13 SCOTT pts/22
sqlplus@goblin.forgotten.realms(TNS V1-V3)
16 SCOTT pts/7
sqlplus@goblin.forgotten.realms(TNS V1-V3)
Related Documents
Note:1020008.6 SCRIPT. FULLY DECODED LOCKING SCRIPT
Note:1054939.6 COMPILATION OF PACKAGE IS HANGING ONLIBRARY CACHE LOCK
=================
library cache pin
=================
原理
~~~~~
An Oracle instance has alibrary cache that contains the description of
erent types of objectse.g. cursors, indexes, tables, views, procedures,
... Those objects cannot bechanged when they are used. They are locked by a
mechanism based on librarylocks and pins. A session that need to use an object
will first acquire a librarylock in a certain mode (null, shared or exclusive)
on the object, in order toprevent other sessions from accessing the same
object (e.g. exclusive lockwhen recompiling a package or view) or to maintain
the object definition for along time. Locking an object is sometimes referred
as the job to it inthe library cache and lock it in a certain mode.
If the session wants tomodify or examine the object, it must acquire after
the lock also a pin in acertain mode (again null, shared or exclusive).
Each SQL statement that wantto use/modify objects that are locked or pinned
and se lock/pin mode isincompatible with the requested mode, will wait
on events like 'library cachepin' or 'library cache lock' until a timeout
occurs. The timeout normallyoccurs after 5 minutes and the SQL statement
then ends with an ORA-4021.If a deadlock is detected, an ORA-4020 is given
back.
Dealing with slow downsrelated to "mysterious" library cache pins
and load locks we should lookfor the reason of the database object
invalidations. They arelikely to be triggered by actions causing
changes to"LAST_DDL" attribute of database objects that have other
dependent ones. Typicallythey are the object maintenance operations -
ALTER, GRANT, REVOKE,replacing views, etc. This behavior. is described
in Oracle Server ApplicationDeveloper's Guide as object dependency
maintenance.
After object invalidation,Oracle tries to recompile the object at the
time of the first access toit. It may be a problem in case when other
sessions have pinned theobject to the library cache. It is obvious that
it is more likely to occurwith more active users and with more complex
dependencies (eg. manycross-dependent packages or package bodies).
In some cases waiting forobject recompilation may even take hours
blocking all the sessionstrying to access it.
ORA-04021 timeout occurredwhile waiting to lock object %s%s%s%s%s".
Cause: Whiletrying to lock a library object, a time-out occurred.
Action: Retry the operationlater.
ORA-04020 deadlock detectedwhile trying to lock object %s%s%s%s%s
Cause: Whiletrying to lock a library object, a deadlock is detected.
Action: Retry the operationlater.
(see
2. Which viewscan be used to detect library locking problems?
----------------------------------------------------------------
Different views can be usedto detect pin/locks:
DBA_KGLLOCK : one row foreach lock or pin of the instance
-KGLLKUSE sessionaddress
-KGLLKHDL Pin/Lockhandle
-KGLLKMOD/KGLLKREQ Holding/requestedmode
0 no lock/pin held
1 null mode
2 share mode
3 exclusive mode
-KGLLKTYPE Pin/Lock
(created via the $ORACLE_HOME/rdbms/admin/catblock.sql)
V$ACCESS : one row for eachobject locked by any user
-SID session sid
-OWNER username
-OBJECT objectname
-TYPE object type
V$DB_OBJECT_CACHE : one rowfor each object in the library cache
-OWNER object owner
-NAME object name or cursor text
-TYPE object type
-LOCKS number of locks on this object
-PINS number of pins on this object
DBA_DDL_LOCKS :one row for each object that is locked (exception made of the cursors)
-SESSION_ID
-OWNER
-NAME
-TYPE
-MODE_HELD
-MODE_REQUESTED
V$SESSION_WAIT : each sessionwaiting on a library cache pin or lock is blocked by some other session
-p1 = object address
-p2 = lock/pin address
3. How to findout why an ORA-4021 occurs?
--------------------------------------------
When you execute thestatement that generates the ORA-4021, it is possible
during the delay of 5 minutesto detect the reason for the blocking situation.
Following query can be usedto find the blocking and waiting sessions:
FYI: You need to run thescript. called "catblock.sql" first.
=== This scriptcan be found in: $ORACLE_HOME/rdbms/admin/catblock.sql
select /*+ ordered */w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
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
from dba_kgllockw, dba_kgllock h, v$session w1, v$session h1
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
/
The result looks like:
WAITING_SESSIONHOLDING_SESSION LOCK ADDRESS MODE_HELD MODE_REQU
------------------------------ ---- -------- --------- ---------
16 12 Pin 03FA2270 Share Exclusive
The object that is locked canbe found with v$object_dependency and
should be the same as the onementioned in the ORA-4021 error message.
e.g.
select to_name fromv$object_dependency where to_address = '03FA2270';
should give:
TO_NAME
-------------
DBMS_PIPE
You can find which library objectsare used by each session via following
queries, e.g.
a. for the blocked session:
select distinct kglnaobj fromx$kgllk where
kgllkuse in (select saddrfrom v$session where sid = 16);
b. for the blocking session
select distinct kglnaobj fromx$kgllk where
kgllkuse in (select saddrfrom v$session where sid = 12);
One of those objects can bethe cursor or statement that each session is
executing/trying to execute.
You can also use the$ORACLE_HOME/rdbms/admin/utld.sql utility to find out
how the dependency tree lookslike and which objects are dependent on e.g.
DBMS_PIPE. One of thoseobjects will be the sql statement of the holding
session. A variant script. onutldtree.sql stands in [NOTE:139594.1] and
gives which objects an objectdepends on.
Library cache pins are used to manage library cacheconcurrency.
Pinning an object causes the heaps to be loaded into memory(if not already loaded).
PINS can be acquired in NULL, SHARE or EXCLUSIVE modes andcan be considered like a special form. of lock.
A wait for a "library cache pin" implies some othersession holds that PIN in an incompatible mode.
P1 = Handle address
P2 = Pin address
P3 = Encoded Mode & Namespace
·Handle address
~~~~~~~~~~~~~~~~
Use P1RAW rather than P1
This is the handle of the library cache object which thewaiting session wants to acquire a pin on.
查詢library cache物件
~~~~~~~~~~~~~~
The actual object being waited on can be found using
SELECT kglnaown "Owner", kglnaobj"Object"
FROM x$kglob
WHERE kglhdadr='&1RAW'
;
·Pin address
~~~~~~~~~~~~~
Use P2RAW rather than P2
This is the address of the PIN itself.
·Encoded Mode & Namespace
~~~~~~~~~~~~~~~~~~~~~~~~~
In Oracle 7.0 - 8.1.7 inclusive the value is 10 * Mode +Namespace.
In Oracle 9.0 - 9.2 inclusive the value is 100 * Mode +Namespace.
Where:
Mode is the mode in which the pin is wanted. This is a numberthus:
o 2 - Share mode
o 3 - Exclusive mode
Namespace is just the namespace number of the namespace inthe library cache in which the required object lives:
o 0 SQL Area
o 1 Table / Procedure / Function /Package Header
o 2 Package Body
o 3 Trigger
o 4 Index
o 5 Cluster
o 6 Object
o 7 Pipe
o 13 Java Source
o 14 Java Resource
o 32 Java Data
====================
library cache lock的成因和解決方法
====================
兩種原因
~~~~~~~~~
* a DML operation that ishanging because the table which is accessed is currently
undergoingchanges (ALTER TABLE). This may take quite a long time depending on
the size of thetable and the type of the modification
(e.g. ALTERTABLE x MODIFY (col1 CHAR(200) on thousands of records).
* The compilation of packagewill hang on Library Cache Lock and Library Cache Pin
if some users areexecuting any Procedure/Function defined in the same package.
兩種解決辦法
~~~~~~~~~~~~~~~
METHOD 1: SYSTEMSTATEANALYSIS
------------------------------
One way of finding thesession blocking you is to analyze the system state dump.
Using the systemstate eventone can create a tracefile containing detailed
information on every Oracleprocess. This information includes all the resources
held & requested by aspecific process.
Whilst an operation ishanging, open a new session and launch the following
statement:
ALTER SESSION SET EVENTS'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 8';
Oracle will now create asystemstate tracefile in your USER_DUMP_DEST directory.
Get the PID (ProcessID) ofthe 'hanging' session from the V$PROCESS by matching
PADDR from V$SESSION withADDR from V$PROCESS:
SELECT PID FROM V$PROCESSWHERE ADDR=
(SELECT PADDR FROM V$SESSION WHERE SID=sid_of_hanging_session);
The systemstate dump containsa separate section with information for each
process. Open the tracefileand do a search for 'PROCESS pid_from_select_stmt'.
In the process section lookup the wait event by doing a search on 'waiting for'.
Example output:
PROCESS 8:
----------------------------------------
SO: 50050b08,type: 1, owner: 0, flag: INIT/-/-/0x00
(process) raclepid=8, calls cur/top: 5007bf6c/5007bf6c, flag: (0) -
int error: 0, call error: 0, sess error: 0,txn error 0
(post info) lastpost received: 82 0 4
last post received-location:kslpsr
last process to post me: 5004ff081 2
last post sent: 0 0 13
last post sent-location: ksasnd
last process posted by me:5004ff08 1 2
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 50058ac4
O/Sinfo: user: daemon, term: pts/1, ospid: 15161
OSDpid info: 15161
----------------------------------------
SO:5005f294, type: 3, owner: 50050b08, flag: INIT/-/-/0x00
(session) trans: 0, creator: 50050b08, flag: (41) USR/-BSY/-/-/-/-/-
DID: 0001-0008-00000002,short-term DID: 0000-0000-00000000
txn branch: 0
oct: 6, prv: 0, user: 41/LC
O/Sinfo: user: daemon, term: pts/1, ospid: 15160, machine: goblin.forgotten.realms
program: sqlplus@goblin.forgotten.realms (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'library cache lock' blocking sess=0x0 seq=253wait_time=0 --等待事件,seq次數,
!>> handle address=5023ef9c, lockaddress=5019cad4, 10*mode+namespace=15
Using the 'handle address'you can look up the process that is kee a lock
on your resource by doing asearch on the address within the same tracefile.
Example output:
PROCESS 9:
----------------------------------------
SO: 50050e08,type: 1, owner: 0, flag: INIT/-/-/0x00
(process) raclepid=9, calls cur/top: 5007bbac/5007bbfc, flag: (0) -
int error: 0, call error: 0, sess error: 0,txn error 0
----------------------------------------
SO: 5019d5e4, type: 34, owner: 5015f65c, flag: INIT/-/-/0x00
!>> LIBRARY OBJECT PIN: pin=5019d5e4 handle=5023ef9c mode=Xlock=0 --handle address
user=5005fad4 session=5005fad4 count=1 mask=0511savepoint=118218 flags=[00]
From the output we can seethat the Oracle process with PID 9 has an exclusive
lock on the object we aretrying to access. Using V$PROCESS and V$SESSION we can
retrieve thesid,user,terminal,program,... for this process. The actual statement
that was launched by thissession is also listed in the tracefile (statements and
other library cache objectsare preceded by 'name=').
METHOD 2: EXAMINE THE X$KGLLKTABLE
-----------------------------------
The X$KGLLK table (accessibleonly as SYS/INTERNAL) contains all the
library object locks (bothheld & requested) for all sessions and
is more complete than theV$LOCK view although the column names don't
always reveal their meaning.
You can examine the locksrequested (and held) by the waiting session
by looking up the sessionaddress (SADDR) in V$SESSION and doing the
following select:
select * from x$kgllk whereKGLLKSES = 'saddr_from_v$session'
This will show you all thelibrary locks held by this session where
KGLNAOBJ contains the first80 characters of the name of the object.
The value in KGLLKHDLcorresponds with the 'handle address' of the
object in METHOD 1.
You will see that at leastone lock for the session has KGLLKREQ > 0
which means this is a REQUESTfor a lock (thus, the session is waiting).
If we now match the KGLLKHDLwith the handles of other sessions in
X$KGLLK that should give usthe address of the blocking session since
KGLLKREQ=0 for this session,meaning it HAS the lock.
SELECT * FROM X$KGLLKLOCK_A
WHERE KGLLKREQ = 0
AND EXISTS(SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES ='saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL =LOCK_B.KGLLKHDL
AND KGLLKREQ > 0);
If we look a bit further wecan then again match KGLLKSES with SADDR
in v$session to find furtherinformation on the blocking session:
SELECTSID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSESFROM X$KGLLK LOCK_A
WHERE KGLLKREQ =0
ANDEXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES ='saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL =LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);
In the same way we can alsofind all the blocked sessions:
SELECTSID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSESFROM X$KGLLK LOCK_A
WHERE KGLLKREQ> 0
ANDEXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES ='saddr_from_v$session' /* BLOCKING SESSION */
AND LOCK_A.KGLLKHDL =LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
);
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7191998/viewspace-749614/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- 定位Library Cache pin,Library Cache lock等待的解決方法
- zt_如何平面解決library cache lock和library cache pin
- library cache pin/lock的解決辦法
- enq：Library cache lock/pin等待事件ENQ事件
- library cache pin和library cache lock的診斷分析
- library cache lock和library cache pin區別總結
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- zt_library cache pin和lock等待分析
- library cache lock\pin的查詢與處理
- 0317Library Cache Pin/Lock Wait EventsAI
- zt_如何使用event 10049分析定位library cache lock and library cache pin
- Shared pool的library cache lock/pin及硬解析
- library cache lock和cursor: pin S wait on X等待AI
- 解決library cache pin等待事件事件
- Library cache pin/lock 在Oracle 10g的增強Oracle 10g
- sql version count引發cursor:pin s wait x及library cache latch library cache lockSQLAI
- 分析解決因”library cache pin”等待
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle
- 【等待事件】library cache pin事件
- 等待事件--library cache pin事件
- library cache pin等待分析
- library cache pin 等待事件事件
- 由最長SQL想到的Latch Free( Library Cache Pin/Lock)整理~~草稿SQL
- 11G資料庫之library cache lock及library cache pin模擬結合hanganalyze定位資料庫
- 【ASK_ORACLE】Library cache pin 與 library load lock的關係和區別Oracle
- 記一次library cache pin事件解決事件
- 共享池之六:shared pool latch/ library cache latch /lock pin 簡介
- LIBRARY CACHE LOCK 等待事件事件
- library cache pin 阻塞程式查詢
- 查詢Library Cache Pin等待原因
- latch:library cache lock等待事件事件
- oracle異常:library cache lockOracle
- DBA手記（學習）-library cache pin
- library cache pin等待事件的模擬事件
- [Oracle]--Library cache lock 故障解決一例Oracle
- 一次library cache pin故障的解決過程