定位Library Cache pin,Library Cache lock等待的解決方法

  這個星期一我去客戶巡檢時，客戶告知我們派綜系統資料庫在前面幾個月
   日誌中有時會出現很多Waited too long for library cache load lock錯誤
  因為當時不在現場，所以無法確定是由什麼引起的。
  可以建議客戶在出現此類情況時,用以下sql進行監控,捕獲相關資訊進行分析：
    這三個sql已經經過測試，沒有問題。
  1.獲得Library Cache pin,Library Cache lock等待的物件,其中kglnaobj是object_name
spool object_name.log
SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
  FROM x$kglob
 WHERE kglhdadr IN (SELECT p1raw
                      FROM v$session_wait
                     WHERE event LIKE 'library%')
spool off;     

2.獲得持有等待物件的session資訊
spool session.log
SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,
       b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq
  FROM v$session a, x$kglpn b
 WHERE a.saddr = b.kglpnuse
   AND b.kglpnmod <> 0
   AND b.kglpnhdl IN (SELECT p1raw
                        FROM v$session_wait
                       WHERE event LIKE 'library%')
spool off;     

3.獲得持有物件使用者執行的程式碼
spool sql_code.log
SELECT sql_text
  FROM v$sqlarea
 WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (
          SELECT sql_address, sql_hash_value
            FROM v$session
           WHERE SID IN (
                    SELECT SID
                      FROM v$session a, x$kglpn b
                     WHERE a.saddr = b.kglpnuse
                       AND b.kglpnmod <> 0
                       AND b.kglpnhdl IN (SELECT p1raw
                                            FROM v$session_wait
                                           WHERE event LIKE 'library%')))
spool off;