How to Find which Session is Holding a particular Library Cache Lock [ID 122793.1] "Daily translation"--2012-11-13

Vernacular once, simple can be divided into two steps to find

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 000000007DCD4AE0 3 pining

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 B Locking_session from V$session where saddr in (' 0000000084c6b9c0 '))

sid    USERNAME    TERMINAL    program       & nbsp           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
Orac Le Server-standard edition-version 9.2.0.1 and later
information in this document applies to any platform.
Purpose Purpose

In some situations it could happen that your session was hanging and is waiting for a ' Library cache lock '. This document, describes how to find the session, is holdig the lock, that's waiting for.

In some environments it will take your session card over there and wait for an event "LIBRARY CACHE LOCK". This document describes how to find the session that takes up the lock 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 are accessed is currently undergoing changes (ALTER table). This could take quite a long time depending on the size of the table and the type of the modification (e.g. ALTER table x MO Dify (col1 CHAR) on a table with thousands of records)

In this case, the V$lock would show that the session doing the ' ALTER TABLE ' with a exclusive DML enqueue LOCK on the TABLE ob Ject (lmode=6, Type=tm where ID1 is the object_id of the table). The waiting session however does not show on v$lock yet so in an environment with a lot of concurrent sessions the V$lo CK information'll be insufficient to track down the culprit blocking your operation. The compilation of package would hang on Library cache Lock and Library cache Pin if any users is executing a PROCEDURE/FU Nction defined in the same package

Common Scenarios

#一个DML操作挂起因为当前访问的表在经历修改 (ALTER TABLE). This may take a long time, depending on the size of the table and the type of modification (such as ALTER TABLE x MODIFY (col1 CHAR (200) There are thousands of rows of records on the table)

in this case, V$lock will show that the session is doing "ALTER table" with exclusive DML on the column lock on the Table object (Lmode=6,type=tm ID1 is the object_id field of the table). The wait session is not displayed in the

V$lock view, so in an environment where there are many concurrent sessions, V$lock's information will not be sufficient to track down the culprits that hinder your operation.

#包的编译将因为LIBRARY the cache lock and library cache PIN events are suspended if any user executes a procedure/function defined in the same package body.

Method 1:systemstate Analysis

SystemState event would create a tracefile containing detailed information on every Oracle process. This information includes all the resources held & requested by a specific process.
Method 1: System State Analysis
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's also possible in higher versions. The level 266 are not available before 9.2.0.6
For older versions of DB, you can use the following syntax, which can also be used in higher versions. Level 266 is not available before 9.2.0.6.
Alter session set max_dump_file_size=unlimited;
Alter session SET Events ' immediate trace name systemstate level 10 '

Oracle would create a systemstate tracefile in your user_dump_dest directory.
Oracle will create a system state trace file in your User_dump_dest directory.
Get the PID (ProcessID) of the ' Hanging ' session:

--Get 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 sections search for the wait event by doing a search on ' waiting for '.
The dump of the system state contains a separate part of the information for each process.

Open the trace file and perform a search of "PROCESS pid"--pid for the PID found above

Look for a wait event about ' waiting for ' in the process section information.
PROCESS:
----------------------------------------
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>--Fragment

(session) sid:141 Trans: (nil), creator:0x7d2bd820, flag: (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 to find information about a locked object
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 was being requested in Shared mode (request=s)
Name of the The object is SCOTT. EMPLOYEES
We see that the object lock of the library is being requested in shared mode (request=s)

The name of the object is called Scott.employees
Use the ' handle address ' to find the process which is holding the lock on your resource by doing a search on the address W Ithin the same tracefile.

#使用 ' HANDLE address ' to find the process that holds the resource lock and find the address in the same trace file.
PROCESS:
----------------------------------------
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=75fe8f7cHANDLE=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 of we can see the Process of (PID)   is holding  a exclusive lock (mode=x) on the Obje CT We is trying to access. Using v$process and V$session We can retrieve the SID, user, terminal, program,... for this PROCESS.
 
The actual statement that were launched by this session are also listed in the Tracefile (statements and Oth ER Library cache objects is preceded by ' name= ').
from the output we can see that the process (PID) is occupying an exclusive lock (MODE=X) on the object we are trying to access. With V$process and v$session we can return to Sid,user,teminal,program,... For this process.
  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 are 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$sessi On and doing the following select:
The Table X$kgllk table (sys/internal access only) contains all of the library object locks (occupied and required) for all sessions and compared to v$lock it is more comprehensive, except that the column name usually does not show its meaning.
Select Sid,saddr from v$session where event= ' library cache lock ';

SID saddr
---------- --------
572ed244


Select Kgllkhdl handle,kgllkreq Request, Kglnaobj Object
From x$kgllk where kgllkses = ' 572ed244 '
and Kgllkreq > 0;

HANDLE REQUEST OBJECT
-------- ---------- ------------------------------------------------------------
62D064DC 2 EMPLOYEES

This'll show you the library caches lock requested by this session (Kgllkreq > 0) where Kglnaobj contains the first 80 Characters of the name of the object. The value of KGLLKHDL corresponds with the ' handle address ' of the object in Method 1 systemstate analysis as shown above.
It shows the lock required for library cache lock for this user (kgllkreq>0), Kglnaobj contains the first 80 characters of the object name. The value KGLLKHDL corresponds to the ' HANDLE ADDRESS ' of the object in System analysis Method 1 above.
If We now match the KGLLKHDL and the handles of other sessions in X$kgllk that should give us the address of the blocking Session. The session holding the lock would have kgllkmod > 0 as it is holding the lock.
If we now go to other users in X$kgllk that match the KGLLKHDL value, it will give the address of the blocking session. This session occupies a lock that will own Kgllkmod "0 when it occupies the lock.
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 block ing session:
If we look farther, we can get further information that matches the value of kgllkses and saddr in V$session to discover blocking sessions.

Select Sid,username,terminal,program from v$session where saddr = ' 572eac94 '

SID USERNAME TERMINAL
---------- ------------------------------ ------------------------------
Program
------------------------------------------------
SCOTT PTS/20
Sqlplus@goblin.forgotten.realms (TNS v1-v3)

In the same to also find all blocked sessions:
We can also query all blocked sessions in the same way:
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
------------------------------------------------
SCOTT PTS/22
Sqlplus@goblin.forgotten.realms (TNS v1-v3)

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 are hanging on LIBRARY CACHE LOCK