如何查找 library locking 问题

以下这些视图都会记录  pins/locks
DBA_KGLLOCK : one row for each lock or pin of the instance
-KGLLKUSE  session address
-KGLLKHDL  Pin/Lock handle
-KGLLKMOD/KGLLKREQ  Holding/requested mode
0           no lock/pin held
1           null mode
2           share mode
3           exclusive mode
-KGLLKTYPE Pin/Lock
(created via the $ORACLE_HOME/rdbms/admin/catblock.sql)

 

V$ACCESS : one row for each object locked by any user
-SID       session sid
-OWNER     username
-OBJECT    object name
-TYPE      object type
 V$DB_OBJECT_CACHE : one row for each object in the library cache
-OWNER         object owner
-NAME          object name or cursor text
-TYPE          object type
-LOCKS         number of locks on this object
-PINS          number of pins on this object

 

DBA_DDL_LOCKS  : one row for each object that is locked (exception made of the cursors)
-SESSION_ID
-OWNER
-NAME
-TYPE
-MODE_HELD
-MODE_REQUESTED

 

V$SESSION_WAIT : each session waiting on a library cache pin or lock is blocked by some other session
-p1 = object address
-p2 = lock/pin address

以下sql可以查出等待和持有锁的会话和类型 library locking

select /*+ ordered */ w1.sid  waiting_session,
         h1.sid  holding_session,
         w.kgllktype lock_or_pin,
         w.kgllkhdl address,
         decode(h.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
            'Unknown') mode_held,
         decode(w.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
          'Unknown') mode_requested
   from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
  where
   (((h.kgllkmod != 0) and (h.kgllkmod != 1)
      and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
    and
      (((w.kgllkmod = 0) or (w.kgllkmod= 1))
      and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
   and  w.kgllktype      =  h.kgllktype
   and  w.kgllkhdl =  h.kgllkhdl
   and  w.kgllkuse     =   w1.saddr
   and  h.kgllkuse     =   h1.saddr
 /

输出的结果如下:

WAITING_SESSION HOLDING_SESSION LOCK ADDRESS  MODE_HELD MODE_REQU
 --------------- --------------- ---- -------- --------- ---------
              16              12 Pin  03FA2270 Share     Exclusive

select to_name from v$object_dependency where to_address = '03FA2270';

可以查出是什么对象，比如存储过程，包等

这个可以查出等待会话所执行的DDl语句
select distinct kglnaobj from x$kgllk  where
kgllkuse in (select saddr from v$session where sid = 16);

这个可以查出持有者会话所执行的DDl语句
select distinct kglnaobj from x$kgllk  where
kgllkuse in (select saddr from v$session where sid = 12);

以下对象会被锁住在library cache

Following list give all objects that can be locked in the library cache:

- CURSOR
- INDEX
- TABLE
- CLUSTER
- VIEW
- SYNONYM
- SEQUENCE
- PROCEDURE
- FUNCTION
- PACKAGE
- PACKAGE BODY
- TRIGGER
- TYPE
- TYPE BODY
- OBJECT
- USER
- DBLINK
- PIPE
- TABLE PARTITION
- INDEX PARTITION
- LOB
- LIBRARY
- DIRECTORY
- QUEUE
- INDEX-ORGANIZED TABLE
- REPLICATION OBJECT GROUP
- REPLICATION PROPAGATOR

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-1758918/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-1758918/