ora-04031錯誤解決方法及詳細分析
2024-07-21 02:37:51
供稿：網友
對于大多數應用來說，共享池的大小對于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 – 這個參數指定了共享池的大小，單位是字節?？梢越邮軘底种祷蛘邤底趾竺娓虾缶Y"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 PERMANENT memory occurs for buffer handles _db_handles_cached = 0 901/ 8172
<Bug:1640583> ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access Not available 8171/901
<Bug:1318267> INSERT AS SELECT statements may
not be shared when they should be
if TIMED_STATISTICS. It can lead to ORA-4031 _SQLEXEC_PROGRESSION_COST=0
8171/8200
<Bug:1193003> Cursors may not be shared in 8.1
when they should be Not available 8162/8170/ 901
共享池結構中的一些BUG 會引起這個錯誤，不過通常大量的共享的SQL/PLSQL 語句也會引起這個錯誤。一旦打過了最新的補丁，在碰到這個問題的時候我們強烈推薦調整數據庫和應用。
要得到已知的BUG 的完整信息，可以參考：
<Note: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 (在數據庫不能找到一個連續的空閑內存塊的時候)。
假如SHARED_POOL_SIZE 足夠大，大多數的 ORA-04031 錯誤都是由共享池中的動態SQL碎片導致的。可能的原因如下：
◇非共享的SQL
◇生成不必要的解析調用 (軟解析)
◇沒有使用綁定變量
要減少碎片的產生你需要確定是前面描敘的幾種可能的因素?？梢圆扇∪缦碌囊恍┓椒?，當然不只局限于這幾種: 應用調整、數據庫調整或者實例參數調整。
下面的視圖有助于你標明共享池中非共享的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
要計算最適合當前工作負荷的共享池大小，參考：
