如何查詢 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