buffer cache 和shared pool詳解 診斷和解決ORA-04031 錯誤

  老熊

http://blog.csdn.net/panfelix/article/details/38347853

ORA-04031這個錯誤，幾乎每一個專業的DBA都遇到過。這是一個相當嚴重的錯誤，Oracle程式在向SGA申請記憶體時，如果申請失敗，則會報這個錯誤。大部分情況下是在向SGA中的shared pool申請記憶體時失敗，而少有向large pool等池中申請記憶體失敗。比如下面的報錯：

這裡很清楚地表示出來，是在向shared pool申請記憶體時失敗。

shared pool記憶體申請（分配）失敗，通常有如下的幾種可能：

• shared pool過小，比如在SGA Manual Management方式下，shared pool設定過小。比如一套數千連線的大系統，shared pool只設定了幾百M。這種情況下，要解決問題很解單，增加shared pool的大小即可。
• 應用沒有使用繫結變數，硬解析非常多，導致shared pool記憶體碎片嚴重，分配大塊記憶體時不能獲得連續的記憶體空間。硬解析多的一個變種是雖然使用了繫結變數，但是由於某種原因，Cursor不能共享，導致Child Cursor非常多。實際上，如果shared pool較大（比如數GB大小），這種問題還是很少出現的，並且出現也通常出現在申請大塊記憶體時。這種情況如果使用alter system flush shared_pool可以暫時緩解問題。但是這條命令又通常不適用於shared pool較大而且比較繁忙的系統。使用繫結變數
• Cache的cursor很多，同時cursor_space_for_time這一引數設定為TRUE，可能會使shared pool碎片化嚴重，導致不能分配到大塊的連續記憶體。
• Oracle的BUG導致記憶體洩露，比如在一些版本中查詢v$segment_statistics這樣的檢視導致記憶體洩露，使shared pool記憶體耗光。同樣的情形還有類似於“obj stat memory”,"gcs resources","ges resources"等。通常這類記憶體為perm型別（permanet），這類記憶體通常是在分配時就確定了固定的用途，不能用於其他用途，因此極容易產生碎片。
• Oracle從9i開始，根據shared pool的大小將shared pool分為多個子池（subpool)，每個子池有獨立的free list，同時在分配時單獨管理（有其獨立 的shared pool latch）。Oracle的BUG或者說是記憶體分配策略缺陷導致某一類shared pool的記憶體分配只在一個子池(subpool)中，即多個子池的使用極不均衡，導致向那個使用得最多的子池申請記憶體時失敗。報錯資訊中的"sga heap(3,0)"即指明是在第3個子池申請記憶體時失敗。本文案例中的ORA-04031錯誤其產生的原因可以歸結為Oracle對shared pool的分配/使用策略問題。
• 作業系統記憶體不足，這隻會出現在shared pool的使用還沒有達到最大值時才會出現，並且在作業系統都有swap的情況下，只有部分作業系統才可能有這種情況，比如在HP-UX下，reserved 記憶體過多導致swap滿。
• 其他原因，多數是因為BUG。請參考下面提及的MOS參考文件。

本文中的案例，其資料庫是執行在AIX 5.3系統中的10.2.0.4 RAC，RAC節點數為2。資料庫是從9i升級到10g，而目前處於正式升級前的測試階段。資料庫報的ORA-04031錯誤資訊如本文前面所示（其中的資料庫名稱已經做了處理）。

在繼續講解案例之前，不得不提到MOS上的幾篇關於ORA-04031錯誤的文件：

• Master Note for Diagnosing ORA-4031 [ID 1088239.1]
• Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools [Video] [ID 146599.1]
• Interpreting the automatically generated ORA-4031 diagnostic trace. [ID 809560.1]
• Troubleshooting and Diagnosing ORA-4031 Error [Video] [ID 396940.1]
• ORA-4031 Common Analysis/Diagnostic Scripts [Video] [ID 430473.1]

其實分析ORA-04031錯誤，通常有以下幾個要點：

• 判斷錯誤發生所有的記憶體區域，是shared pool,large pool還是streams pool等。這個很容易從錯誤資訊中判斷出來，本文主要描述shared pool的ORA-04031錯誤，這也是最常見的。
• 檢查Shared Pool的總大小以及free memory的大小。如果free memory看上去挺多，以subpool為單位檢查是否存在是由於碎片導致沒有足夠的連續記憶體以供分配，特別是關注報錯資訊中提及的子池。
• 如果Shared Pool相較於系統規模來說足夠大（通常數GB都已經是很大的了），檢查Shared Pool中有沒有佔用非常多的記憶體型別或記憶體元件，如果有，是什麼樣的型別的記憶體，在各個子池之間是否分佈均勻。如果有異常佔用較多的記憶體型別，根據此型別在MOS上搜尋是否是會有相應的BUG引起，或者分析這種型別的記憶體消耗較多的原因。比如如果是sql area很大，檢查是不是硬解析特別多，或者是不是child cursor特別多引起。
• 基於以上分析的資料，來判斷shared pool記憶體分配失敗的原因。

上面的步驟寫得比較粗略，關於分析和解決ORA-04031問題，這裡也有一篇不錯的文章：Simplified Approach to Resolve ORA-4031

這裡關鍵的是分析Shared Pool的記憶體資料。ORA-04031錯誤發生後如果有條件可以馬上連線到資料庫中查詢相應的x$表和v$檢視得到相應的資料，否則只能透過ORA-4031錯誤發生時產生的trace檔案。_4031_dump_bitvec這個隱含引數用於控制發生ORA-04031錯誤時對SGA的dump行為，而trace檔案的分析就不像使用SQL那樣簡單了。

下面再來詳細地分析案例：
從錯誤資訊來看，很顯然，是向shared pool的第3個subpool申請記憶體時出錯。
以下的資料是shared pool的資料：

雖然free的數量不是太多，但是freeable的數量還是很多的。
下面是各個子池更詳細的資料：

KSMCHDUR是什麼意思？在9i裡面這個列的值通常為1。實際上，Oracle從9i開始，將shared pool劃分為多個sub pool。而在10g以上的版本中（具體開始的版本號已經不記得），每個sub pool又分了4個更小的池，我們暫且稱之為mini heap。每個mini heap有其自己的free list。KSMCHDUR這一列即表示mini heap的編號。"heap(3,0)"中的0是指第1個mini heap。

在上面的資料中，可以看到這個子池的第1個mini heap的free已經很少，只有10來K。另外，我們可以觀察到，perm型別的記憶體塊只存在於每個sub pool的第1個min heap中。這個是一個重點，在後面會有解釋。

這裡本應該有透過查詢v$sgastat得到shared pool的各個元件佔用的記憶體分佈，只是寫BLOG時找不到了....但是我們可以在trace檔案中找到資料，下面只列出sub pool 3的資料：

從上面的資料可以看到，第3個sub pool中，佔用較多的記憶體是gcs resources、gcs shadows以及sql area。但是沒有明顯的異常。
下面是第3個sub pool中第1個mini-heap中free memory的更詳細資料：

可以看到，最大的free memory塊才2032位元組，而報錯中提到的申請的記憶體大小為4128位元組。由於在第3個sub pool的第1個mini heap中沒有4128位元組的連續free memory，所以導致記憶體申請失敗。

那麼這裡的問題是，為什麼這個mini heap中的free memory那麼少？正如前面提及，為什麼這個mini heap中的已經使用的型別全是perm型別？這個問題的答案就在於"DURATION"。Oracle在啟用了SGA自動管理的模式下，為了便於在shared pool與buffer cache或其他記憶體之間動態調整大小，規定了在每一個mini heap中分配記憶體按照duration來進行。這裡duration可以理解為記憶體塊的持久時間。perm型別的記憶體塊，就是分配後不能釋放，只能用於相同元件的重用。比如gcs resources這種元件的記憶體是perm型別，這種記憶體被分配後，不能釋放給sql area使用，也不能給gcs shadows使用，只能給其他的gcs resource使用。按DURATION分配記憶體時，perm型別的記憶體就只能從每個sub pool的第1個mini heap中分配。而其他型別的記憶體通常在sub pool的第2-4個mini heap中分配。由於perm型別的記憶體不能釋放，也不能被其他元件的記憶體重用，所以裡面的記憶體會越用越少，如果沒有了free memory怎麼辦？前面說到，這種模式主要是工作在SGA自動管理模式下，如果free memory沒有了，就會從SGA中的其他部分，比如buffer cache中取得memory chunk，加入到缺少記憶體的mini heap中。正常情況下這種機制沒有問題。

完全使用SGA自動管理有一個缺陷就是，如果應用系統繫結變數做得不好，或者由於BUG，child cursor過多，導致shared pool會變得很大，甚至超過10G，嚴重的比buffer cache還大，另一方面，在buffer cache和shared pool之間頻繁地調整大小，可能會導致嚴重的解析問題和其他效能問題。針對這個問題，通常有2種解決辦法：一種就是關閉SGA自動管理，即將SGA_TARGET設定為0，以9i的方式來設定shared_pool_size,db_cache_size這些引數，來手動管理SGA；第二種就是sga_target仍然大於0，即自動管理SGA，但是透過設定shared_pool_size，db_cache_size等引數限制這些記憶體元件的最小大小，而只留給系統極少的自動調整空間。

而出現問題的這套系統，正是使用了第二種方式，開啟了SGA自動調整，但是留給自動調整的空間極少。SGA_TARGET為35G，buffer_cache_size為30G，shared_pool_size為4G，再加上large_pool等元件，幾乎沒有什麼可自動調整的餘地。這種方式下，就存在了問題。下面來做一個按時間的分析：

1. 時間T1，資料庫啟動，shared pool只消耗了極少量的記憶體。
2. 時間T2至時間T3，Oracle程式請求shared pool記憶體，Oracle會向作業系統以指定的粒度為單位（比如16MB）請求實體記憶體，加入到所請求記憶體所在的mini heap中。直至shared pool的大小達到shared pool最大容許的大小。這個容許大小由各引數計算而來。比如說SGA_TARGET為10G，其他元件的引數設定後最小值為8G，shared_pool_size的值為1G，但是shared pool的最大容許大小為2G。這個時候，每個sub pool的mini heap的大小已經固定。在到達shared pool最大容許大小這一階段，可能會從buffer cache等元件中佔用。
3. 時間T4，Oracle程式請求shared pool記憶體，這個時候只能從free list或age out記憶體塊後獲取記憶體，對於sub pool的第1個mini heap，只能從free list中獲取，因為這個mini heap中的已用記憶體全是perm，是不能age out的。
4. 時間T5，Oracle程式請求shared sub pool中第1個mini heap的記憶體，但是free list中已經沒有記憶體。所以報ORA-04031錯誤。

在上面的時間點T5那裡，如果SGA有較大的自動調整空間，比如說完全沒有限制，即buffer_cache_size等引數很少或為0，這樣在請求第1個mini heap中的記憶體時，完全可以從buffer cache中佔用，這樣的後果是使shared pool越來越大。

而本文案例的ORA-04031，正是由於SGA自動管理，而自動調整的餘地又太小，最終使sub pool的第1個mini heap空間用光。當然我們可以分析為什麼會用光，這個就顯得更為複雜，這跟資料量、應用系統都有很大的關係。而系統中第1次出現ORA-04031錯誤的程式，是一個job程式，而此後大部分出現的錯誤均是job程式，能檢查job程式碼，發現在做大量的表的大量資料的UPDATE操作，這可能是引起gcs shadows和gcs resources大量記憶體使用的原因。在一套RAC資料庫中，gcs和ges相關的perm記憶體佔用可能會比較大。

那麼除了調整應用，應該怎麼樣解決這樣問題？這裡的解決方法是增加shared_pool_size引數到6G，同時將sga_target設定為0，再重啟。
而另一種可能的辦法是將引數“_enable_shared_pool_durations"設定為FALSE。這一引數為FALSE，將會使shared pool記憶體分配時，不再使某一型別的記憶體(比如perm)必須要求在一個固定的mini heap中。而實際上，sga_target設定為0之後，這一個引數自動會設為FALSE（由於這一引數是靜態引數，所以修改了sga_target之後需要重啟才會使這個隱含引數改變），所以建議的解決辦法是設定sga_target引數，而不建議修改隱含引數。當然還有一種辦法是完全讓Oracle自動管理SGA，將buffer_cache_size和shared_pool_size等引數設定為0，但是正如前面所說，這種方法有比較大的缺陷。