Oracle 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  支援人員來解決問題。