在dbsnake 上看到的這篇文章，轉(zhuǎn)過來。 主要還是學(xué)習(xí)解決問題的一個思路。這個往往比問題的解決更重要。

原文鏈接如下：

http://dbsnake.com/2010/06/solve-library-cache-pin.html

內(nèi)容如下：

今天接到同事的電話，說他的一個存儲過程已經(jīng)run了一個多小時了，還在繼續(xù)run，他覺得極不正常，按道理說不應(yīng)該run這么長時間。

我說那我去看一下吧。

這個庫是一個AIX上的10.2.0.4，我采集了一下問題時間段的AWR報(bào)告：

從AWR報(bào)告結(jié)果里我們可以看出在出問題的時間段， 系統(tǒng)在經(jīng)歷嚴(yán)重的library cache pin以及l(fā)ibrary cache lock等待。

根據(jù)Load Profile的信息， 看出導(dǎo)致上述library cache pin和library cache lock的并不是hardparse。

對于library cache pin等待來說，AWR報(bào)告的作用有限，最有效的方式就是找到持有l(wèi)ibrary cache pin以及等待library cache pin的session，然后看看他們在做什么：

SQL> SELECT s.sid, kglpnmod"Mode", kglpnreq "Req", SPID "OS Process"

2 FROM v$session_wait w, x$kglpn p,v$session s ,v$process o

3 WHERE p.kglpnuse=s.saddr

4 AND kglpnhdl=w.p1raw

5 and w.event like '%library cache pin%'

6 and s.paddr=o.addr

7 /

SID Mode Req OS Process

---------- ---------- ---------- ------------

396 0 2 6381970

396 0 2 6381970

396 0 2 6381970

396 0 2 6381970

341 2 0 4092132

341 2 0 4092132

341 2 0 4092132

341 2 0 4092132

363 0 2 3514690

363 0 2 3514690

363 0 2 3514690

363 0 2 3514690

304 0 2 3977478

304 0 2 3977478

304 0 2 3977478

304 0 2 3977478

354 0 3 3137874

354 0 3 3137874

354 0 3 3137874

354 0 3 3137874

20 rows selected

我那位run存儲過程的同事所在的session是396，從上述結(jié)果里我們可以看出來396現(xiàn)在想以Share模式（即Req=2）去持有l(wèi)ibrary cache pin，同時現(xiàn)在持有上述library cache pin的是session 341，且341的持有模式也是Share（即Mode=2）。

本來Share和Share是可以共享的，但不幸的是在396之前，session 354想以Exclusive模式（即Req=3）去持有上述librarycache pin，這直接導(dǎo)致了396需要處于等待的Queue中，同時處于Queue中的還有363和304。

我為什么這么說呢，因?yàn)閛racle對library cache pin的解釋中有一句非常經(jīng)典的話：

An X request (3) will be blocked by anypins held S mode (2) on the object.
An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.

所以從AWR報(bào)告和上述查詢結(jié)果中我們可以得出如下結(jié)論：

1、 我那位run存儲過程的同事為什么run了1個多小時還沒有run完是因?yàn)檫@個存儲過程正在經(jīng)歷嚴(yán)重的library cache pin等待；

2、 而為什么會導(dǎo)致嚴(yán)重的library cache pin等待是因?yàn)閟ession 341和354聯(lián)手達(dá)到了這一效果，即341以Share模式持有l(wèi)ibrary cache pin，接著354想以Exclusive模式持有，這直接導(dǎo)致所有的后續(xù)請求全部被處于等待的Queue中。 也就是說341阻塞了354，而354又間接阻塞了396。

既然知道了原因，那我們?nèi)タ匆幌聅ession 341在做什么事情：

SQL> selectdecode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session wheresid=341;

DECODE(SQL_HASH_VALUE,0,PREV_H

------------------------------

784727971

間隔10秒鐘后再次執(zhí)行：--間隔10秒查詢的目的是判斷session 的內(nèi)容是否改變，從而判斷出session 在干什么

SQL> selectdecode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session wheresid=341;

DECODE(SQL_HASH_VALUE,0,PREV_H

------------------------------

784727971

間隔10秒鐘后再次執(zhí)行：

SQL> selectdecode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session wheresid=341;

DECODE(SQL_HASH_VALUE,0,PREV_H

------------------------------

784727971

SQL> select sql_text from v$sqltextwhere hash_value=784727971 order by piece;

SQL_TEXT

----------------------------------------------------------------

begin -- Call the procedurep_adj_rrp_main(o_vc_flag => :o_vc_flag); end;

從結(jié)果里可以看到341一直在run一個存儲過程。

給持有341的那位大姐打電話，問她在做什么，她告訴我說她從昨晚就開始run這個存儲過程，今早來看發(fā)現(xiàn)死掉了，所以她就沒管了。

知道原因后處理起來還是很容易的，當(dāng)我把session 341干掉后，整個系統(tǒng)的library cache pin一下子就降下來了，接著我那位同事的run了一個多小時的存儲過程過了沒多久就run完了。

-------------------------------------------------------------------------------------------------------

Blog： http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(滿); DBA2 群：62697977(滿)DBA3 群：62697850(滿)

DBA 超級群：63306533(滿); DBA4 群： 83829929 DBA5群： 142216823

DBA6 群：158654907 聊天 群：40132017 聊天2群：69087192

--加群需要在備注說明Oracle表空間和數(shù)據(jù)文件的關(guān)系，否則拒絕申請

一次library cache pin故障的解決過程