案例分析：ora-04031與ora-04030錯誤分析與解決

一：錯誤總述

1． ORA-04031
基本上，ORA-04031出現的問題有幾個可能性
A. 沒有繫結編量造成shared_pool碎片過多，同時shared_pool_size太小.

--這個應該是比較常見的，也是Oracle提的最多的。
--這個通常會建議使用繫結變數，或者簡單的加大shared_pool.或者臨時解決方法就是alter system flush shared_pool.

B. Large_pool,Java_pool太小造成的

--這個通過錯誤資訊的提示很容易判斷(Ora-04031 cannot allocate .. memeory in [large_pool])
--解決方法就是簡單的加大 Large_pool or Java_pool

C. 過度的開CURSOR而不關閉。

--這個問題發生的越來越多，特別是在JAVA執行環境中，頻頻出現。加大Shared_pool或者flush shared_pool往往只能延遲問題出現的時間，而沒法避免。
--判斷方法：
select count(*) from v$open_cursor ;
select * from v$sysstat
where name = 'opened cursors current';
如果出來的值特大（以萬為單位）時，基本就可以確定是這個原因了
--解決這個問題的方法就是檢查程式，看是否沒有正常的關閉cursor(對於JAVA來說，就是沒有關閉Statement)。或者select sql_text from v$open_cursor,看看都是哪些cursor沒關閉，再去檢查車程式。
--也有的程式使用了保持一定量的cursor一直open,從而避免cursor過多次的開啟，來提高效能。對於這種情況，則應該選擇適當的shared_pool_size和控制keep_opening的cursor的量。
--也有可能Oracle引數session_cached_cursors太大，解決方法就是把它降低到適當的值

樓主的問題似乎有點象 session_cached_cursors 的問題，但是根據 opened cursors current判斷，每個session開的cursor超過1000，已經超過session_cached_cursors，應該檢查程式看看。

D. 當然，有時候一些BUG也可能引發ORA-04031,但是在高版本中已經很少出現(>=8174)


2。ORA-04030
ORA-04030出現的基本都是過多的使用memory造成的 。

ORA-04030的問題一般是PGA過度分配造成的（對應的操作是sort/hash_join）。在Oracle9i中pga_aggregate_target指定了所有session總共使用的最大PGA上限，如果該值被設定了則預設的workarea_size_policy=auto, sort_area_size/sort_area_retained_size將被忽略。那麼直接減小pga_aggregate_target就能解決一部分ORA-04030問題。

A. 對於32 BIT系統，有SGA 1.7G限制
B. 某些OS系統本身也有一些記憶體引數限制
--執行 ulimit 看看
C. OS系統本身實體記憶體+Swap的限制


我們應該檢查DB使用的
SGA + PGA 是否超過 上面的限制

SGA 包括 db_cache,shared_pool,large_pool,java_pool
session的PGA包括
sort_area_size/Hash_area_size/*_area_size 或者 pga_aggregate_target
還有執行的CODE以及一些data也會佔用空間。

然後再根據情況降低裡面的某些值了，比如 db_cache， sort_area_size 等


應該是sort_area_size（200M）/Hash_area_size(400M) 太大造成的，降成幾十M或者幾M 就可以了。

二：診斷並解決ORA-04031 錯誤

當我們在共享池中試圖分配大片的連續記憶體失敗的時候，Oracle首先清除池中當前沒使用的所有物件，使空閒記憶體塊合併。如果仍然沒有足夠大單個的大塊記憶體滿足請求,就會產生ORA-04031 錯誤。

當這個錯誤出現的時候你得到的錯誤解釋資訊類似如下:

04031, 00000, "unable to allocate %s bytes of shared memory (/"%s/",/"%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;

10g 註釋：Oracle 10g 的一個新特性叫做 "自動記憶體管理" 允許DBA保留一個共享記憶體池來分shared pool,buffer cache, java pool 和large pool。一般來說，當資料庫需要分配一個大的物件到共享池中並且不能找到連續的可用空間，將自動使用其他SGA結構的空閒空間來增加共享池的大小 。既然空間分配是Oracle自動管理的，ora-4031出錯的可能性將大大降低。自動記憶體管理在初始化引數SGA_TARGET大於0的時候被啟用。當前設定可以通過查詢v$sga_dynamic_components 檢視獲得。請參考10g管理手冊以得到更多內容 。

2.診斷ORA-04031 錯誤

注：大多數的常見的 ORA-4031 的產生都和 SHARED POOL SIZE 有關，這篇文章中的診斷步驟大多都是關於共享池的。　對於其它方面如Large_pool或是Java_pool，記憶體分配演算法都是相似的，一般來說都是因為結構不夠大造成。

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

ORACLE BUG

Oracle推薦對你的系統打上最新的PatchSet。大多數的ORA-04031錯誤都和BUG 相關，可以通過使用這些補丁來避免。

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

BUG	描述	Workaround	Fixed
<1397603>	ORA-4031/SGA memory leak of PERMANENT memory occurs for buffer handles	_db_handles_cached = 0	901/ 8172
<1640583>	ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access	Not available	8171/901
<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
<1193003>	Cursors may not be shared in 8.1 when they should be	Not available	8162/8170/ 901
<2104071>	ORA-4031/excessive "miscellaneous" shared pool usage possible. (many PINS)	None-> This is known to affect the XML parser.	8174, 9013, 9201
<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 引數的值，然後重啟動系統，再試一下。參考: <2736601> 。

· 小的共享池尺寸

很多情況下，共享池過小能夠導致ORA-04031錯誤。下面資訊有助於你調整共享池大小：

庫高速緩衝命中率

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

SELECT SUM(PINS) "EXECUTIONS",

SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"

FROM V$LIBRARYCACHE;

如果丟失超過1%,那麼嘗試通過加大共享池的大小來減少庫高速緩衝丟失。

共享池大小計算

要計算最適合你工作負載的共享池大小，請參考：

<1012046.6>: HOW TO CALCULATE YOUR SHARED POOL SIZE.

· 共享池碎片

每一次，需要被執行的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;

這個表只可以用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);

4. ORA-04031 錯誤與 Large Pool

大池是個可選的記憶體區，為以下的操作提供大記憶體分配：

· MTS會話記憶體和 Oracle XA 介面

· Oracle 備份與恢復操作和I/O伺服器程式用的記憶體(緩衝)

· 並行執行訊息緩衝

大池沒有LRU列表。這和共享池中的保留空間不同，保留空間和共享池中其他分配的記憶體使用同樣的LRU列表。大塊記憶體從不會換出大池中，記憶體必須是顯式的被每個會話分配並釋放。一個請求如果沒有足夠的記憶體，就會產生類似這樣的一個ORA-4031錯誤：

ORA-04031: unable to allocate XXXX bytes of shared memory

("large pool","unknown object","session heap","frame")

這個錯誤發生時候可以檢查幾件事情：

· 1- 使用如下語句檢查 V$SGASTAT ，得知使用和空閒的記憶體：

· SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool';

· 2- 你還可以採用 heapdump level 32 來 dump 大池的堆並檢查空閒的大塊記憶體的大小

從大池分配的記憶體如果是LARGE_POOL_MIN_ALLOC 子節的整塊數有助於避免碎片。任何請求分配小於LARGE_POOL_MIN_ALLOC 大塊尺寸都將分配LARGE_POOL_MIN_ALLOC的大小。一般來說，你會看到使用大池的時候相對共享池來說要用到更多的記憶體。通常要解決大池中的ORA-4031錯誤必須增加 LARGE_POOL_SIZE 的大小。

5. ORA-04031 和共享池重新整理

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

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

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

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

6. ORA-04031錯誤的高階分析

如果前述的這些技術內容都不能解決ORA-04031 錯誤，可能需要額外的跟蹤資訊來得到問題發生的共享池的快照。

調整init.ora引數新增如下的事件得到該問題的跟蹤資訊：

event = "4031 trace name errorstack level 3"

event = "4031 trace name HEAPDUMP level 3"

如果問題可重現，該事件可設定在會話層，在執行問題語句之前使用如下的語句：

SQL> alter session set events '4031 trace name errorstack level 3';

SQL> alter session set events '4031 trace name HEAPDUMP level 3';

把這個跟蹤檔案發給Oracle支援人員進行排錯。

重要標註: Oracle 9.2.0.5 和Oracle 10g 版本中，每次在發生ORA-4031 錯誤的時候會自動建立一個跟蹤檔案，可以在user_dump_dest 目錄中找到。如果你的系統是上述的版本，你不需要再進行前面描述中的步驟。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28673746/viewspace-760092/，如需轉載，請註明出處，否則將追究法律責任。