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

• 已知的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.