How to Find which Session is Holding a Particular Library Cache Lock
APPLIES TO:
Oracle Database - Personal Edition - Version 9.2.0.1 and laterOracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Database - Standard Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
PURPOSE
Purpose
In some situations it may happen that your session is hanging and is waiting for a 'Library cache lock'. This document describes how to find the session that is holdig the lock that you are waiting for.
Scope and Application
Support Analysts and DBAs
TROUBLESHOOTING STEPS
Common Situations
-
A DML operation that is hanging because the table which is accessed is currently undergoing changes (ALTER TABLE). This may take quite a long time depending on the size of the table and the type of the modification (e.g. ALTER TABLE x MODIFY (col1 CHAR(200) on a table with thousands of records)
In this case, V$LOCK will show that the session doing the 'ALTER TABLE' with an exclusive DML enqueue lock on the table object (LMODE=6, TYPE=TM where ID1 is the OBJECT_ID of the table). The waiting session however does not show up in V$LOCK. So in an environment with a lot of concurrent sessions, the V$LOCK information will be insufficient to track down the culprit blocking your operation.
- The compilation of package will hang on Library Cache Lock and Library Cache Pin if any users are executing a procedure/function defined in the same package.
Method 1: Systemstate Analysis
Systemstate event will create a tracefile containing detailed information on every Oracle process. This information includes all the resources held & requested by a specific process.
While an operation is hanging, open a new session and launch the following statement:
For Oracle 9.2.0.1 or higher:
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
For older versions, you can use the following syntax that is also possible in higher versions.The level 266 is not available before 9.2.0.6
alter session set events 'immediate trace name systemstate level 10'
Oracle will create a systemstate tracefile in your USER_DUMP_DEST directory.
Get the PID (ProcessID) of the 'hanging' session:
(select paddr from v$session where sid= <sid_of_hanging_session> );
Open the tracefile and do a search for "PROCESS <PID from above>".
In the process section, search for the wait event by doing a search on 'waiting for'.
----------------------------------------
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 (TNS V1-V3)
<cut>
(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=0 seconds since wait started=11
handle address=62d064dc, lock address=79f88a68, 100*mode+namespace=c9
-
Use the handle address to find 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) cdp=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
<cut>
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 an exclusive lock (mode=X) on the object we are trying to access. Using V$PROCESS and V$SESSION, we can retrieve the sid, user, terminal, program,... for this process.
The actual statement that was launched by this session is also listed in the tracefile (statements and other library cache objects are preceded by 'name=').
METHOD 2: EXAMINE THE X$KGLLK TABLE
The X$KGLLK table (accessible only as SYS/INTERNAL) contains all the library object locks (both held & requested) for all sessions and is more complete than the 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 by looking up the session address (SADDR) in V$SESSION and doing the following select:
SID SADDR
---------- --------
16 572ed244
select kgllkhdl Handle,kgllkreq Request, kglnaobj Object
from x$kgllk where kgllkses = '572ed244'
and kgllkreq > 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 the object.The value in KGLLKHDL corresponds with the 'handle address' of the object in Method 1 Systemstate Analysis as shown above.
If we now match the KGLLKHDL with the handles of other sessions in X$KGLLK, that should give us the address of the blocking session.The session holding the lock will have KGLLKMOD > 0 as it is holding the lock.
from x$kgllk lock_a
where kgllkmod > 0
and exists (select lock_b.kgllkhdl from x$kgllk lock_b
where kgllkses = '572ed244' /* blocked session */
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq > 0);
SADDR HANDLE MOD
-------- -------- ----------
OBJECT
------------------------------------------------------------
572eac94 62d064dc 3
EMPLOYEES
If we look a bit further, we can then again match KGLLKSES with SADDR in v$session to find further information on the blocking session:
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:
where saddr in
(select kgllkses from x$kgllk lock_a
where kgllkreq > 0
and exists (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)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15747463/viewspace-1799332/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Find which Session is Holding a Particular Library Cache Lock_122793.1Session
- 尋找 library cache lock 等待事件的session事件Session
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- LIBRARY CACHE LOCK 等待事件事件
- library cache pin和library cache lock的診斷分析
- library cache lock和library cache pin區別總結
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- latch:library cache lock等待事件事件
- oracle異常:library cache lockOracle
- 定位Library Cache pin,Library Cache lock等待的解決方法
- zt_如何平面解決library cache lock和library cache pin
- enq:Library cache lock/pin等待事件ENQ事件
- library cache lock 阻塞程式查詢
- Library cache lock/pin詳解(轉)
- 常用定位library cache lock的方法
- LIBRARY CACHE LOCK WAITS AND NO BLOCKER FOUNDAIBloC
- 短連線 引起的 library cache lock
- 查詢library cache lock的源頭
- zt_如何使用event 10049分析定位library cache lock and library cache pin
- 一次library cache lock 問題分析
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- library cache pin/lock的解決辦法
- zt_library cache pin和lock等待分析
- 'library cache lock'等待事件的處理方法事件
- oracle 10049 event之library cache lockOracle
- 俺也談談 library cache lock 等待事件事件
- 深入理解shared pool共享池之library cache的library cache lock系列四
- [20240824]跟蹤library cache lock library cache pin使用gdb.txt
- sql version count引發cursor:pin s wait x及library cache latch library cache lockSQLAI
- [20240920]跟蹤library cache lock library cache pin使用gdb.txt
- [Oracle]--Library cache lock 故障解決一例Oracle
- library cache lock\pin的查詢與處理
- RAC 環境Library Cache Lock的處理方法
- 0317Library Cache Pin/Lock Wait EventsAI
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle
- Shared pool的library cache lock/pin及硬解析
- library cache lock和cursor: pin S wait on X等待AI