【翻译自mos文章】找到持有library cache lock session的方法

找到持有library cache lock session的方法

参考自：
How to Find which Session is Holding a Particular Library Cache Lock (文档 ID 122793.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:

$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

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.

Get the PID (ProcessID) of the ‘hanging‘ session:

select pid from v$process where addr=
 (select paddr from v$session where sid= <sid_of_hanging_session> );

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: [email protected] (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=‘).

二、查询x$kgllk 表

1.
获得等待事件为library cache lock的session的 session address

SQL> select sid,saddr from v$session where event= 'library cache lock';

       SID SADDR
---------- ----------------
       132 000000009F783740
       198 000000009F06AFE0  --->用这个SADDR，这是等待者的SADDR

Elapsed: 00:00:00.04
SQL> 

2.
根据上面的SADDR获得library cache lock涉及到的对象。

SQL> select kgllkhdl Handle,kgllkreq Request, kglnaobj Object  from x$kgllk 
  2  where kgllkses = '000000009F06AFE0'  and kgllkreq > 0;     --->代入上面的SADDR

HANDLE              REQUEST OBJECT
---------------- ---------- ------------------------------
00000000976AE938          3 DUMMY11GR2                   ----->可以看到涉及到的对象

Elapsed: 00:00:00.06
SQL> 

注意：
KGLLKHDL:对应于v$session_wait的p1raw列，表明library cache lock的锁地址。也对应于x$kglob的kglhdadr这一列。

3.
根据上面的等待者的SADDR，获得阻塞者(即:持有者)的信息：saddr，handle，mod，object

SQL> select kgllkses saddr,kgllkhdl handle,kgllkmod mod,kglnaobj object
  2   from x$kgllk lock_a
  3   where kgllkmod > 0
  4   and exists (select lock_b.kgllkhdl from x$kgllk lock_b
  5   where kgllkses = '000000009F06AFE0' /* blocked session */  --->代入上面的SADDR
  6   and lock_a.kgllkhdl = lock_b.kgllkhdl
  7   and kgllkreq > 0);

SADDR            HANDLE                  MOD OBJECT
---------------- ---------------- ---------- ------------------------------
000000009F7773C0 00000000976AE938          1 DUMMY11GR2
000000009F6C3020 00000000976AE938          3 DUMMY11GR2

Elapsed: 00:00:04.07  --->这个时间可能会很长，毕竟这是我的测试环境，不过4个USER session，但是就消耗了4秒钟。

4.
查出阻塞者(即:持有者)的信息：sid,username,terminal,program

SQL> select sid,username,terminal,program from v$session where saddr  in ('000000009F7773C0','000000009F6C3020');

       SID USERNAME                       TERMINAL                       PROGRAM
---------- ------------------------------ ------------------------------ ------------------------------------------------
        71 LC0029999                      pts/0                          sqlplus@rhel63single (TNS V1-V3)
       136 LC0019999                      pts/1                          sqlplus@rhel63single (TNS V1-V3)

Elapsed: 00:00:00.15
SQL> --->找到了2个session，这2个session就是阻塞者(或者说是持有者)session

5.
查出所有的等待者的信息：

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 = '000000009F06AFE0' /* blocking session */
              and lock_a.kgllkhdl = lock_b.kgllkhdl
              and kgllkreq = 0)
 );

附：
---sid 136

SQL> set timing on
SQL> show user
USER is "LC0019999"
SQL> select * from v$mystat where rownum<2; 

       SID STATISTIC#      VALUE
---------- ---------- ----------
       136          0          0

Elapsed: 00:00:00.00
SQL> Begin  
  2  Dummy11gR2;  
  3  Dbms_lock.sleep(3600);  
  4  End;  
  5  /  

---sid 71

SQL> set timing on
SQL> show user
USER is "LC0029999"
SQL> select * from v$mystat where rownum<2; 

       SID STATISTIC#      VALUE
---------- ---------- ----------
        71          0          0

Elapsed: 00:00:00.00
SQL> alter procedure lc0019999.dummy11gR2 compile;
alter procedure lc0019999.dummy11gR2 compile
--->hang住