如何解決ORA-04031 錯誤(zt)

到現在還有不少朋友在問這個04031的老問題。想起來以前翻過這個文件，貼出來，供大家參考。轉載，請註明出處和所有的URL的Link.

翻譯：Fenng

文章內容

1.和共享池(shared pool)相關的例項引數

2.診斷ORA-04031 錯誤

3.解決ORA-04031 錯誤

• 已知的Oracle BUG
• 共享池碎片
-- o V$SQLAREA 檢視
-- o X$KSMLRU 檢視
• 小的共享池尺寸
-- o 庫高速緩衝(library cache)命中率
-- o 共享池大小的計算

4.對ORA-04031 的高階分析

診斷並解決ORA-04031 錯誤

對於大多數應用來說，共享池的大小對於Oracle 效能來說都是很重要的。共享池中儲存資料字典高速緩衝
和完全解析或編譯的的PL/SQL 塊和SQL 語句。

當我們在共享池中試圖分配大片的連續記憶體失敗的時候，Oracle 首先重新整理池中當前沒使用的所有物件,使空
閒記憶體塊合併。如果仍然沒有足夠大單個的大塊記憶體滿足請求,就會產生ORA-04031 錯誤。
當這個錯誤出現的時候你得到的錯誤資訊如下:

Error: ORA 4031
Text: unable to allocate %s bytes of shared memory (%s,%s,%s)
----------------------------------------------------------------------------------------------------------------
Cause: More shared memory is needed than was allocated in the shared pool.
Action: Either use the dbms_shared_pool package to pin large packages, reduce your use of
shared memory, or increase the amount of available shared memory by increasing the value of
the init.ora parameter "shared_pool_size".

1.共享池相關的例項引數

在繼續之前，理解下面的例項引數是很重要的:

• SHARED_POOL_SIZE – 這個引數指定了共享池的大小，單位是位元組。可以接受數字值或者數
字後面跟上字尾"K" 或 "M" 。"K"代表千位元組, "M"代表兆位元組。

• SHARED_POOL_RESERVED_SIZE – 指定了為共享池記憶體保留的用於大的連續請求的共享池
空間。當共享池碎片強制使Oracle 查詢並釋放大塊未使用的池來滿足當前的請求的時候，這個參
數和SHARED_POOL_RESERVED_MIN_ALLOC 引數一起可以用來避免效能下降。

這個引數理想的值應該大到足以滿足任何對保留列表中記憶體的請求掃描而無需從共享池中重新整理對
象。既然作業系統記憶體可以限制共享池的大小，一般來說，你應該設定這個引數為
SHARED_POOL_SIZE 引數的 10% 大小。

• SHARED_POOL_RESERVED_MIN_ALLOC –這個引數的值控制保留記憶體的分配。如果一個足
夠尺寸的大塊記憶體在共享池空閒列表中沒能找到，記憶體就從保留列表中分配一塊比這個值大的空
間。預設的值對於大多數系統來說都足夠了。如果你加大這個值，那麼Oracle 伺服器將允許從這
個保留列表中更少的分配並且將從共享池列表中請求更多的記憶體。這個引數在Oracle 8i 是隱藏
的。

2.診斷ORA-04031 錯誤
ORA-04031 錯誤通常是因為庫高速緩衝中或共享池保留空間中的碎片。 在加大共享池大小的時
候考慮調整應用使用共享的SQL 並且調整如下的引數：

SHARED_POOL_SIZE,
SHARED_POOL_RESERVED_SIZE,
SHARED_POOL_RESERVED_MIN_ALLOC.

首先判定是否ORA-04031 錯誤是由共享池保留空間中的庫高速緩衝的碎片產生的。提交下的查
詢：

SELECT free_space, avg_free_size,used_space,
avg_used_size,request_failures, last_failure_size
FROM v$shared_pool_reserved;

如果:
REQUEST_FAILURES > 0 並且
LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC

那麼ORA-04031 錯誤就是因為共享池保留空間缺少連續空間所致。
要解決這個問題,可以考慮加大SHARED_POOL_RESERVED_MIN_ALLOC 來降低緩衝進共
享池保留空間的物件數目,並增大 SHARED_POOL_RESERVED_SIZE 和
SHARED_POOL_SIZE 來加大共享池保留空間的可用記憶體。

如果：
REQUEST_FAILURES > 0 並且
LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC
或者
REQUEST_FAILURES 等於0 並且
LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

那麼是因為在庫高速緩衝缺少連續空間導致ORA-04031 錯誤。

第一步應該考慮降低SHARED_POOL_RESERVED_MIN_ALLOC 以放入更多的物件到共享池
保留空間中並且加大SHARED_POOL_SIZE。


3.解決ORA-04031 錯誤

• ORACLE BUG
要解決這個錯誤(如果可以稱得上錯誤的話）,進行的診斷的第一步是在你的平臺上使用最新的補丁集。
大多數的ORA-04031錯誤都和BUG 相關，可以透過使用這些補丁來避免。

下面表中總結和和這個錯誤相關的最常見的BUG，可能的環境和修補這個問題的補丁。

程式碼:


BUG-----------------描述-------------------------------－Workaround-----------------Fixed



<Bug:1397603>       ORA-4031 / SGA memory leak of       _db_handles_cached = 0      8.1.7.2 

                    PERMANENT memory occurs for                                     901

                    buffer handles.

             

<Bug:1640583>       ORA-4031 due to leak / cache buffer Not available               8171/901

                    chain contention from AND-EQUAL

                    access



<Bug:1318267>       INSERT AS SELECT statements may     _SQLEXEC_PROGRESSION_COST=0 8171,8200

                    not be shared when they should be

                    if TIMED_STATISTICS. It can lead

                    to ORA-4031





<Bug:1193003>       Cursors may not be shared in 8.1    Not available                8162,8170,901

                    when they should be.

共享池結構中的一些BUG 會引起這個錯誤，不過通常大量的共享的SQL/PLSQL 語句也會引起
這個錯誤。一旦打過了最新的補丁，在遇到這個問題的時候我們強烈推薦調整資料庫和應用。

要得到已知的BUG 的完整資訊，可以參考：<62143.1>: Main issues affecting the Shared
Pool on Oracle 7 , Oracle8 and Oracle8i。


• 共享池碎片
每一次，需要被執行的SQL 或者PL/SQL 語句的解析形式載入共享池中都需要一塊特定的連續
的空間。資料庫要掃描的第一個資源就是共享池中的空閒可用記憶體。一旦空閒記憶體耗盡，資料庫
要查詢一塊已經分配但還沒使用的記憶體準備重用。如果這樣的確切尺寸的大塊記憶體不可用，就繼
續按照如下標準尋找：

◇ 大塊(chunk)大小比請求的大小大
◇ 空間是連續的
◇ 大塊記憶體是可用的(而不是正在使用的)

這樣大塊的記憶體被分開，剩餘的新增到相應的空閒空間列表中。當資料庫以這種方式操作一段時
間之後，共享池結構就會出現碎片。

當共享池存在碎片的問題,分配一片空閒的空間就會花費更多的時間,資料庫效能也會下降(整個操
作的過程中，"chunk allocation"被一個叫做"shared pool latch" 的閂所控制) 或者是出現
ORA-04031 錯誤errors (在資料庫不能找到一個連續的空閒記憶體塊的時候)。

-------------------------------------------------------------------------------------
參考 <61623.1>: 可以得到關於共享池碎片的詳細討論。
-------------------------------------------------------------------------------------
如果SHARED_POOL_SIZE 足夠大，大多數的 ORA-04031 錯誤都是由共享池中的動態SQL
碎片導致的。可能的原因如下：

◇非共享的SQL
◇生成不必要的解析呼叫 (軟解析)
◇沒有使用繫結變數

要減少碎片的產生你需要確定是前面描敘的幾種可能的因素。可以採取如下的一些方法，當然不
只侷限於這幾種: 應用調整、資料庫調整或者例項引數調整。

--------------------------------------------------------------------------------------
請參考 <62143.1>，描述了所有的這些細節內容。這個註釋還包括了共享池如何工作的細節。
--------------------------------------------------------------------------------------

下面的檢視有助於你標明共享池中非共享的SQL/PLSQL：

• V$SQLAREA 檢視

這個檢視儲存了在資料庫中執行的SQL 語句和PL/SQL 塊的資訊。下面的SQL 語句可以
顯示給你帶有literal 的語句或者是帶有繫結變數的語句：

SELECT substr(sql_text,1,40) "SQL", count(*) , sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;

注意: 語句Having 中的 "30"數值可以根據需要調整以得到更為詳細的資訊。

• X$KSMLRU 檢視
有一個固定表x$ksmlru 跟蹤共享池中導致其它物件換出(age out)的應用。這個固定表可
以用來標記是什麼導致了大的應用。

如果很多物件在共享池中都被階段性的重新整理可能導致響應時間問題並且有可能在物件過載
入共享池中的時候導致庫高速緩衝閂競爭問題。

關於這個x$ksmlru 表的一個不尋常的地方就是如果有人從表中選取內容這個表的內容就
會被擦除。這樣這個固定表只儲存曾經發生的最大的分配。這個值在選擇後被重新設定這
樣接下來的大的分配可以被標記，即使它們不如先前的分配過的大。因為這樣的重置，在
查詢提交後的結果不可以再次得到，從表中的輸出的結果應該小心的儲存。
監視這個固定表執行如下操作：

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;

在Oracle8i 中這個表不能被SYS使用者之外的使用者所選取。

• 小的共享池尺寸

最後,一個小的共享池可以導致ORA-04031 錯誤, 不過在碎片真正的是個問題的時候增大
共享池的大小的時候要小心。在錯誤發現的時候通常有延遲現象，不過當在大的共享池的
碎片中找到一片空閒的記憶體會加大對效能的影響。

下面的資訊將有助於你調整共享池的大小：

庫高速緩衝命中率
命中率有助於你衡量共享池的使用，基於多少次SQL/PLSQL 需要被解析而不是
重用。下面的SQL 語句有助於你計算庫高速緩衝的命中率：

SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;

如果misses 比上executions 大於1%, 那就應該嘗試著透過加大共享池來減少庫高速緩衝
的丟失。

Shared Pool Size Calculation
要計算最適合當前工作負荷的共享池大小，參考：
<1012046.6>: HOW TO CALCULATE YOUR SHARED POOL SIZE.

4.對ORA-04031 的高階分析

如果使用如上的解決辦法，這個錯誤仍然出現，在initSID.ora 檔案中設定如下的事件並重新啟
動例項：

event = "4031 trace name errorstack level 3"

會在下一次錯誤發生的時候產生一個跟蹤檔案。
這個跟蹤檔案可以提供給Oracle 支援人員來解決問題。


相關文件
<151790.1> : Oracle8 Tuning Documentation Guide
<62143.1> : Understanding and Tuning the Shared Pool in Oracle7, Oracle8, and Oracle8i.
<1012046.6>: HOW TO CALCULATE YOUR SHARED POOL SIZE
<1012049.6>: TUNING LIBRARY CACHE LATCH CONTENTION
<61623.1> : Resolving Shared Pool Fragmentation In Oracle7
：就是這篇文件的英文原稿．

所有Note字樣的文件可以從Metalink.oracle.com上找到。需要Oracle的CSI帳號。

About:
這篇文章的翻譯是本著學習交流的目的,當然,原英文作者保留版權.如果對譯文上的技術細節不是
很滿意,可以參考原英文文件.有其它問題請透過 Fenng@itpub.net 和我聯絡.

原文出處：

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

格式化了好幾次，也不是很好看

有需要的朋友到去下載吧

如果想徹底的避免04031的問題，編碼的時候就該注意了。

不過類似的問題還是會出現，這似乎是個永恆的話題