HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK
来源:互联网 发布:csol邪皇护甲修正数据 编辑:程序博客网 时间:2024/04/27 17:26
PURPOSE
-------
In some situations it may happen your session is 'hanging' and is a waiting for
a 'Library cache lock'. This document describes how to find the session that
in fact has the lock you are waiting for.
SCOPE & APPLICATION
-------------------
Support analysts, dba's, ..
HOW TO FIND THE SESSION HOLDING A A LIBRARY CACHE LOCK
------------------------------------------------------
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 thousands of records).
* The compilation of package will hang on Library Cache Lock and Library Cache Pin if some users are executing any Procedure/Function defined in the same package.
In the first situation the V$LOCK view will show that the session doing the
'ALTER TABLE' has an exclusive DML enqueue lock on the table object (LMODE=6,
TYPE=TM and 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 is insufficient to track down the culprit blocking your
operation.
METHOD 1: SYSTEMSTATE ANALYSIS
------------------------------
One way of finding the session blocking you is to analyze the system state dump.
Using the systemstate event one can create a tracefile containing detailed
information on every Oracle process. This information includes all the resources
held & requested by a specific process.
Whilst an operation is hanging, open a new session and launch the following
statement:
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';
Oracle will now create a systemstate tracefile in your USER_DUMP_DEST directory.
Get the PID (ProcessID) of the 'hanging' session from the V$PROCESS by matching
PADDR from V$SESSION with ADDR from V$PROCESS:
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_select_stmt'.
In the process section look up the wait event by doing a search on 'waiting for'.
Example output:
PROCESS 8:
----------------------------------------
SO: 50050b08, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=8, calls cur/top: 5007bf6c/5007bf6c, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 82 0 4
last post received-location: kslpsr
last process to post me: 5004ff08 1 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/S info: user: daemon, term: pts/1, ospid: 15161
OSD pid 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/S info: 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=253 wait_time=0
!>> handle address=5023ef9c, lock address=5019cad4, 10*mode+namespace=15
Using the 'handle address' you can look up the process that is keeping a lock
on your resource by doing a search on the address within the same tracefile.
Example output:
PROCESS 9:
----------------------------------------
SO: 50050e08, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=9, calls cur/top: 5007bbac/5007bbfc, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
<cut> ....
----------------------------------------
SO: 5019d5e4, type: 34, owner: 5015f65c, flag: INIT/-/-/0x00
!>> LIBRARY OBJECT PIN: pin=5019d5e4 handle=5023ef9c mode=X lock=0
user=5005fad4 session=5005fad4 count=1 mask=0511 savepoint=118218 flags=[00]
From the output we can see that the Oracle process with PID 9 has an exclusive
lock 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:
select * from x$kgllk where KGLLKSES = 'saddr_from_v$session'
This will show you all the library locks held by this session 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.
You will see that at least one lock for the session has KGLLKREQ > 0
which means this is a REQUEST for a lock (thus, the session is waiting).
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 since
KGLLKREQ=0 for this session, meaning it HAS the lock.
SELECT * FROM X$KGLLK LOCK_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 we can then again match KGLLKSES with SADDR
in v$session to find further information on the blocking session:
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 = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);
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 = 'saddr_from_v$session' /* BLOCKING SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
);
阅读全文
0 0
- HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK
- How to Find which Session is Holding a Particular Library Cache Lock (文档 ID 122793.1)
- How to Find which Session is Holding a Particular Library Cache Lock
- How to Find which Session is Holding a Particular Library Cache Lock [ID 122793.1]【每日一译】--2012-11-13
- Search Results for: library How to trouble shooting Library cache lock/pin
- [转]How to flush a storage volume's file cache, lock and dismount it, and eject the media
- How to find the file which contain a specified string
- Database Performance: How to reduce "library cache: mutex X" waits for scalability in the Oracle dat
- 如何找出有Library Cache Lock锁的Session
- how to find the badi?
- library cache pin/lock
- how to lock statistics for the tables
- library cache pin和library cache lock
- library cache lock和library cache pin
- How do I lock the orientation to portrait mode in a iPhone Web Application?
- EclipseLink How to disable the shared cache?
- How To Clear A Browser Cache
- how to use the boost library
- 创建索引
- wpscloudsvr.exe 怎么删除
- Java集合---ConcurrentHashMap原理分析
- 实用工具-在Mac OS环境下配置Web和PHP服务器
- 沉浸式状态栏的设置
- HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK
- 从 MVC 到前后端分离
- angular入门-实现简单的switch
- How to Determine The Lock Type and Mode from an Enqueue Wait
- 数据应用达人之SQL基础教程分享4-键的约束
- Unet Unity3d
- httpclient4.x处理https协议请求
- 欢迎使用CSDN-markdown编辑器
- newifi新路由如何限速?