白话一遍，简单可分两步去查找
1.
select kgllkses saddr,kgllkhdl handle,kgllkmod mod,kglnaobj object
from x$kgllk lock_a
where kgllkmod > 0
and exists (select 1 from x$kgllk lock_b
where kgllkses in (select saddr from v$session where event= 'library cache lock')
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq > 0)
SADDR HANDLE MOD OBJECT
0000000084C6B9C0
2.
select sid,username,terminal,program,sql_id ,blocking_session from v$session where saddr in ('0000000084C6B9C0')
union all
select sid,username,terminal,program,sql_id ,blocking_session from v$session
where sid in (select blocking_session from v$session where saddr in ('0000000084C6B9C0'))
SID USERNAME TERMINAL PROGRAM SQL_ID BLOCKING_SESSION
194 AIKI pts/3 sqlplus@kfctest (TNS V1-V3) axdyhbwz084bp 209
209 AIKI pts/2 sqlplus@kfctest (TNS V1-V3) 5whptv3nstk28
Applies to:
Oracle Server - Enterprise Edition - Version 9.2.0.1 and later
Oracle Server - Personal Edition - Version 9.2.0.1 and later
Oracle Server - 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.
在一些环境它将发生你的会话卡在那并且等待一个事件“LIBRARY CACHE LOCK'。这个文档描述了如何找到占用你等待锁的会话。
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 yet 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
常见情形
#一个DML操作挂起因为当前访问的表在经历修改（ALTER TABLE).这个可能会花费很长的时间，它取决于表的大小和修改的类型（如ALTER TABLE x MODIFY (col1 CHAR(200)在表上有几千行的记录）
在这种情况下，V$LOCK将显示会话正在做”ALTER TABLE“采用独占DML对列锁在表对象上（LMODE=6,TYPE=TM ID1是表的OBJECT_ID字段).等待会话不会显示在
V$LOCK视图，所以在存有许多并发会话的环境里，V$LOCK的信息将不足以跟踪到阻碍你操作的元凶。
#包的编译将因为LIBRARY CACHE LOCK和LIBRARY CACHE PIN事件挂起，如果任何用户执行一个过程/函数在同一个包体中的定义。
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.
方法1：系统状态分析
While an operation is hanging, open a new session and launch the following statement:
For Oracle 9.2.0.1 or higher:
$sqlplus '/ as sysdba'
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
对于老版本的DB，你可以使用如下的语法，它也可以用于高版本中。266级别在9.2.0.6之前是不可用的。
alter session set max_dump_file_size=unlimited;
alter session set events 'immediate trace name systemstate level 10'
Oracle will create a systemstate tracefile in your USER_DUMP_DEST directory.
ORACLE将创建一个系统状态的跟踪文件在你的USER_DUMP_DEST目录里。
Get the PID (ProcessID) of the 'hanging' session:
--获取PID
select pid from v$process where addr=
(select paddr from v$session where sid= <sid_of_hanging_session> );
The systemstate dump contains a separate section with information for each process.
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'.
系统状态的DUMP包含一个独立的部份的信息对于每一个进程。
打开跟踪文件并且执行一个关于”PROCESS PID“的搜索 --PID为上面找出的PID
在进程部份信息里查找关于‘WAITING FOR’的等待事件。
PROCESS 20:
----------------------------------------
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:
#使用HANDLE ADDRESS去查找被锁对象的信息
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
我们看到LIBRARY的对象锁正被要求以共享模式（REQUEST=S)
对象的名称叫做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.
#使用‘HANDLE ADDRESS'去查找占有资源锁的进程，在同一个跟踪文件里查找地址。
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=').
从输出中我们可以看到PROCESS 18(PID)正占有排它锁（MODE=X）在我们试图访问的对象上。使用V$PROCESS和V$SESSION我们可以返回SID,USER,TEMINAL,PROGRAM,...对于这个进程。
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:
表X$KGLLK表（仅SYS/INTERNAL访问）包含了所有的库对象锁（占有的和要求的）对于所有的会话并且对比于V$LOCK它更全面的，只是列名通常不能显示它的意思。
select sid,saddr from v$session where event= 'library cache lock';
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.
它显示了库缓存锁要求的锁对于这个用户（KGLLKREQ>0），KGLNAOBJ包含了对象名称的前80个字符。值KGLLKHDL对应于对象的’HANDLE ADDRESS‘ 在上面的系统分析方法1中。
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.
如果我们现在在X$KGLLK中去匹配KGLLKHDL值的其它用户，它将给出阻塞会话的地址。这个会话占有锁将拥有KGLLKMOD》0 当它占有锁时。
select kgllkses saddr,kgllkhdl handle,kgllkmod mod,kglnaobj object
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:
如果我们看了更远一点，我们可以获得匹配KGLLKSES和在V$SESSION中的SADDR的值去发现阻塞会话的进一步信息。
select sid,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:
用同样的方法我们也可以查询所有被阻塞的会话：
select sid,username,terminal,program from v$session
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)
Related Documents
Note:1020008.6 SCRIPT FULLY DECODED LOCKING SCRIPT
Note:1054939.6 COMPILATION OF PACKAGE IS HANGING ON LIBRARY CACHE LOCK
