一個儲存過程編譯HANG住的分析

轉白老師的文章。

客戶的一個庫編譯某個儲存過程HANG住。檢視了一下，也沒鎖方面沒有什麼問題。檢視v$session_wait,發現在等待Library cache pin。

建議他做一個3級的hanganalyze。結果如下：

==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : :
<48/29750/0x3ed60060/7856/PX Deq: Join ACK>
-- <141/31823/0x3ed5d680/2291/library cache pin> --被HANG住的SESSIONChain 2 : :
<76/16377/0x3ed554b0/6507/No Wait>
Chain 3 : :
<98/8617/0x3ed65c80/4550/No Wait>
Chain 4 : :
<117/43613/0x3ed55080/6505/No Wait>
Other chains found:
Extra information that will be dumped at higher levels:
[level 4] : 4 node dumps -- [LEAF] [LEAF_NW] [IGN_DMP]
[level 5] : 1 node dumps -- [NLEAF]
[level 10] : 78 node dumps -- [IGN]

State of nodes
([nodenum]/sid/sess_srno/session/state/start/finish/[adjlist]/predecessor):
[0]/1/1/0x3ee65290/IGN/1/2//none
[1]/2/1/0x3ee65c10/IGN/3/4//none
[2]/3/1/0x3ee66590/IGN/5/6//none
[3]/4/1/0x3ee66f10/IGN/7/8//none
[4]/5/1/0x3ee67890/IGN/9/10//none
[5]/6/1/0x3ee68210/IGN/11/12//none
[6]/7/1/0x3ee68b90/IGN/13/14//none
[8]/9/28264/0x3ee69e90/IGN/15/16//none
[9]/10/45631/0x3ee6a810/IGN/17/18//none
[10]/11/53468/0x3ee6b190/IGN/19/20//none
[11]/12/1/0x3ee6bb10/IGN/21/22//none
[12]/13/1/0x3ee6c490/IGN/23/24//none
[13]/14/39041/0x3ee6ce10/IGN/25/26//none
[16]/17/12002/0x3ee6ea90/IGN/27/28//none
[17]/18/26556/0x3ee6f410/IGN/29/30//none
[19]/20/7116/0x3ee70710/IGN/31/32//none
[20]/21/57406/0x3ee71090/IGN/33/34//none
[21]/22/43997/0x3ee71a10/IGN/35/36//none
[22]/23/38835/0x3ee72390/IGN/37/38//none
[23]/24/50707/0x3ee72d10/IGN/39/40//none
[24]/25/37623/0x3ee73690/IGN/41/42//none
[27]/28/25010/0x3ee75310/IGN/43/44//none
[28]/29/11415/0x3ee75c90/IGN/45/46//none
[30]/31/56343/0x3ee76f90/IGN/47/48//none
[31]/32/32598/0x3ee77910/IGN/49/50//none
[33]/34/60664/0x3ee78c10/IGN/51/52//none
[39]/40/15619/0x3ee7c510/IGN/53/54//none
[40]/41/5750/0x3ee7ce90/IGN/55/56//none
[43]/44/37048/0x3ee7eb10/IGN/57/58//none
[44]/45/10277/0x3ee7f490/IGN/59/60//none
[46]/47/49868/0x3ee80790/IGN/61/62//none
[47]/48/29750/0x3ee81110/LEAF/63/64//140
[50]/51/19008/0x3ee82d90/IGN/65/66//none
[54]/55/45303/0x3ee85390/IGN/67/68//none
[55]/56/12036/0x3ee85d10/IGN/69/70//none
[56]/57/35536/0x3ee86690/IGN/71/72//none
[59]/60/16507/0x3ee88310/IGN/73/74//none
[61]/62/1474/0x3ee89610/IGN/75/76//none
[62]/63/11770/0x3ee89f90/IGN/77/78//none
[64]/65/6967/0x3ee8b290/IGN/79/80//none
[65]/66/22994/0x3ee8bc10/IGN/81/82//none
[66]/67/29782/0x3ee8c590/IGN/83/84//none
[69]/70/8179/0x3ee8e210/IGN/85/86//none
[70]/71/64787/0x3ee8eb90/IGN/87/88//none
[73]/74/14398/0x3ee90810/IGN/89/90//none
[74]/75/52024/0x3ee91190/IGN/91/92//none
[75]/76/16377/0x3ee91b10/LEAF_NW/93/94//none
[76]/77/13846/0x3ee92490/IGN/95/96//none
[80]/81/8490/0x3ee94a90/IGN/97/98//none
[81]/82/14268/0x3ee95410/IGN/99/100//none
[83]/84/15851/0x3ee96710/IGN/101/102//none
[84]/85/46327/0x3ee97090/IGN/103/104//none
[88]/89/30265/0x3ee99690/IGN/105/106//none
[91]/92/43886/0x3ee9b310/IGN/107/108//none
[94]/95/38899/0x3ee9cf90/IGN/109/110//none
[95]/96/53153/0x3ee9d910/IGN/111/112//none
[97]/98/8617/0x3ee9ec10/LEAF_NW/113/114//none
[98]/99/23949/0x3ee9f590/IGN/115/116//none
[99]/100/18928/0x3ee9ff10/IGN/117/118//none
[100]/101/8726/0x3eea0890/IGN/119/120//none
[106]/107/26774/0x3eea4190/IGN/121/122//none
[107]/108/11313/0x3eea4b10/IGN/123/124//none
[108]/109/53920/0x3eea5490/IGN/125/126//none
[109]/110/43621/0x3eea5e10/IGN/127/128//none
[111]/112/45774/0x3eea7110/IGN/129/130//none
[113]/114/10520/0x3eea8410/IGN/131/132//none
[114]/115/41649/0x3eea8d90/IGN/133/134//none
[116]/117/43613/0x3eeaa090/LEAF_NW/135/136//none
[118]/119/18074/0x3eeab390/IGN/137/138//none
[119]/120/44156/0x3eeabd10/IGN/139/140//none
[120]/121/25600/0x3eeac690/IGN/141/142//none
[122]/123/60946/0x3eead990/IGN/143/144//none
[124]/125/26026/0x3eeaec90/IGN/145/146//none
[126]/127/28459/0x3eeaff90/IGN/147/148//none
[128]/129/22272/0x3eeb1290/IGN/149/150//none
[129]/130/19774/0x3eeb1c10/IGN/151/152//none
[130]/131/2674/0x3eeb2590/IGN/153/154//none
[134]/135/36030/0x3eeb4b90/IGN/155/156//none
[135]/136/48766/0x3eeb5510/IGN/157/158//none
[137]/138/60856/0x3eeb6810/IGN/159/160//none
[138]/139/37634/0x3eeb7190/IGN/161/162//none
[140]/141/31823/0x3eeb8490/NLEAF/163/164/[47]/none
[142]/143/8778/0x3eeb9790/IGN/165/166//none
*** WARNING: no system state dumped ***
====================
END OF HANG ANALYSIS
====================

檢查48號SESSION，是P01程式，在等待PX Deq: Join ACK 。狀態是KILLED。看到上述資訊，就明白了，由於PX SLAVE程式異常退出時，未正常釋放資源導致LIBRARY CACHE PIN未釋放。這個時候找到作業系統pid，直接在作業系統kill -9 ，問題就解決了。

我的評論：又見library cache pin，最近我與它是過不去了。

[@more@]