ORA-4031解決思路

 

診斷並解決 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: If the shared pool is out of memory, 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 parameters   "shared_pool_reserved_size" and           "shared_pool_size".  If the large pool is out of memory, increase   the INIT.ORA  parameter   "large_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 和更高的版本中是隱藏的。提交如下的語句查詢這個引數值 :

SELECT    nam . ksppinm NAME, val . ksppstvl VALUE       FROM x$ksppi nam , x$ksppsv val      WHERE nam . indx = val . indx AND nam . ksppinm LIKE '%shared%' ORDER BY 1 ;

 

2. 診斷 ORA-04031 錯誤

ORA-04031 可能是因為 SHARED POOL 不夠大，或是因為碎片問題導致資料庫不能找到足夠大的記憶體塊。

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 錯誤

A．          ORACLE BUG 安裝相應 patch

B．          共享池碎片透過 ALTER SYSTEM FLUSH SHARED_POOL

C．          增加 shared_pool_size

• ORACLE BUG
要解決這個錯誤 ( 如果可以稱得上錯誤的話） , 進行的診斷的第一步是在你的平臺上使用最新的補丁集。大多數的 ORA-04031 錯誤都和 BUG 相關，可以透過使用這些補丁來避免。
Oracle 推薦對你的系統打上最新的 PatchSet. 大多數的 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:2104071>  ORA-4031/excessive "miscellaneous" shared pool usage possible. (many PINS) None-> This is known to affect the XML parser.  8174, 9013, 9201
<Note:263791.1>  Several number of BUGs related to ORA-4031 erros were fixed in the 9.2.0.5 patchset Not available 9205

編譯 Java 程式碼時出現的 ORA-4031

　　在你編譯 Java 程式碼的時候如果記憶體溢位，你會看到錯誤：

　　 A SQL exception occurred while compiling ： ：

　　 ORA-04031 ： unable to allocate bytes of shared memory

　　（ "shared pool" ， "unknown object" ， "joxlod ： init h" ， "JOX ： ioc_allocate_pal" ）

　　解決辦法是關閉資料庫然後把引數 JAVA_POOL_SIZE 設定為一個較大的值。這裡錯誤資訊中提到的 "shared pool" 其實共享全域性區（ SGA ）溢位的誤導，並不表示你需要增加 SHARED_POOL_SIZE ，相反，你必須加大 JAVA_POOL_SIZE 引數的值，然後重啟動系統，再試一下。參考： <Bug ： 2736601> .

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

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

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

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

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

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

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

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

--------------------------------------------------------------------------------------
請參考 <Note: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 ；

　　這個表只可以用 SYS 使用者登入進行查詢。

　　 X$KSMSP 檢視 （類似堆 Heapdump 資訊）

使用這個檢視能找出當前分配的空閒空間，有助於理解共享池碎片的程度。如我們在前面的描述，查詢為遊標分配的足夠的大塊記憶體的第一個地方是空閒列表（ free list ）。 下面的語句顯示了空閒列表中的大塊記憶體：

SELECT     '0 (<140)' bucket, ksmchcls, 10 * TRUNC (ksmchsiz / 10) "From",            COUNT (*) "Count",   MAX (ksmchsiz) "Biggest",            TRUNC (AVG (ksmchsiz))   "AvgSize", TRUNC (SUM (ksmchsiz)) "Total"       FROM x$ksmsp      WHERE ksmchsiz < 140 AND ksmchcls = 'free'   GROUP BY ksmchcls, 10 * TRUNC (ksmchsiz / 10)   UNION ALL   SELECT   '1 (140-267)' bucket, ksmchcls, 20 * TRUNC (ksmchsiz /   20),            COUNT (*), MAX (ksmchsiz),   TRUNC (AVG (ksmchsiz)) "AvgSize",            TRUNC (SUM (ksmchsiz))   "Total"       FROM x$ksmsp      WHERE ksmchsiz BETWEEN 140 AND 267 AND ksmchcls = 'free'   GROUP BY ksmchcls, 20 * TRUNC (ksmchsiz / 20)   UNION ALL   SELECT   '2 (268-523)' bucket, ksmchcls, 50 * TRUNC (ksmchsiz /   50),            COUNT (*), MAX (ksmchsiz),   TRUNC (AVG (ksmchsiz)) "AvgSize",            TRUNC (SUM (ksmchsiz))   "Total"       FROM x$ksmsp      WHERE ksmchsiz BETWEEN 268 AND 523 AND ksmchcls = 'free'   GROUP BY ksmchcls, 50 * TRUNC (ksmchsiz / 50)   UNION ALL   SELECT   '3-5 (524-4107)' bucket, ksmchcls, 500 * TRUNC (ksmchsiz /   500),            COUNT (*), MAX (ksmchsiz),   TRUNC (AVG (ksmchsiz)) "AvgSize",            TRUNC (SUM (ksmchsiz))   "Total"       FROM x$ksmsp      WHERE ksmchsiz BETWEEN 524 AND 4107 AND ksmchcls = 'free'   GROUP BY ksmchcls, 500 * TRUNC (ksmchsiz / 500)   UNION ALL   SELECT   '6+ (4108+)' bucket, ksmchcls, 1000 * TRUNC (ksmchsiz /   1000),            COUNT (*), MAX (ksmchsiz),   TRUNC (AVG (ksmchsiz)) "AvgSize",            TRUNC (SUM (ksmchsiz))   "Total"       FROM x$ksmsp      WHERE ksmchsiz >= 4108 AND ksmchcls = 'free'   GROUP BY ksmchcls, 1000 * TRUNC (ksmchsiz / 1000);

• 小的共享池尺寸

最後 , 一個小的共享池可以導致 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
要計算最適合當前工作負荷的共享池大小，參考：
<Note:1012046.6>: HOW TO CALCULATE YOUR SHARED POOL SIZE.

4 ． ORA-04031 和共享池重新整理

有一些技巧會提高遊標的共享能力，從而共享池碎片和 ORA-4031 都會減少。最佳途徑是調整應用使用繫結變數。另外在應用不能調整的時候考慮使用 CURSOR_SHARING 引數和 FORCE 不同的值來做到 （要注意那會導致執行計劃改變，所以建議先對應用進行測試）。當上述技巧都不可以用的時候，並且碎片問題在系統中比較嚴重，重新整理共享池（ alter system flush SHARED_POOL ）可能有助於減輕碎片問題。但是，必須加以如下考慮：

　　重新整理將導致所有沒被使用的遊標從共享池刪除。這樣，在共享池重新整理之後，大多數 SQL 和 PL/SQL 遊標必須被硬解析。這將提高 CPU 的使用，也會加大 Latch 的活動。

　　當應用程式沒有使用繫結變數並被許多使用者進行類似的操作的時候（如在 OLTP 系統中） ，重新整理之後很快還會出現碎片問題。所以共享池對設計糟糕的應用程式來說不是解決辦法。

　　對一個大的共享池重新整理可能會導致系統掛起，尤其是例項繁忙的時候，推薦在非高峰的時候重新整理

5. 對 ORA-04031 的高階分析

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

event = "4031 trace name errorstack level 3"

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