診斷并解決ORA-04031 錯(cuò)誤

當(dāng)我們在共享池中試圖分配大片的連續(xù)內(nèi)存失敗的時(shí)候，Oracle首先清除池中當(dāng)前沒使用的所有對象，使空閑內(nèi)存塊合并。如果仍然沒有足夠大單個(gè)的大塊內(nèi)存滿足請求,就會產(chǎn)生ORA-04031 錯(cuò)誤。

當(dāng)這個(gè)錯(cuò)誤出現(xiàn)的時(shí)候你得到的錯(cuò)誤解釋信息類似如下:

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.共享池相關(guān)的實(shí)例參數(shù)

在繼續(xù)之前，有必要理解下面的實(shí)例參數(shù):

SHARED_POOL_SIZE

這個(gè)參數(shù)指定了共享池的大小，單位是字節(jié)。可以接受數(shù)字值或者數(shù)字后面跟上后綴"K" 或 "M" 。"K"代表千字節(jié), "M"代表兆字節(jié)。

SHARED_POOL_RESERVED_SIZE

指定了為共享池內(nèi)存保留的用于大的連續(xù)請求的共享池空間。當(dāng)共享池碎片強(qiáng)制使 Oracle 查找并釋放大塊未使用的池來滿足當(dāng)前的請求的時(shí)候，這個(gè)參數(shù)和SHARED_POOL_RESERVED_MIN_ALLOC 參數(shù)一起可以用來避免性能下降。

這個(gè)參數(shù)理想的值應(yīng)該大到足以滿足任何對保留列表中內(nèi)存的請求掃描而無需從共享池中刷新對象。既然操作系統(tǒng)內(nèi)存可以限制共享池的大小，一般來說，你應(yīng)該設(shè)定這個(gè)參數(shù)為 SHARED_POOL_SIZE 參數(shù)的 10% 大小。

SHARED_POOL_RESERVED_MIN_ALLOC 這個(gè)參數(shù)的值控制保留內(nèi)存的分配。如果一個(gè)足夠尺寸的大塊內(nèi)存在共享池空閑列表中沒能找到，內(nèi)存就從保留列表中分配一塊比這個(gè)值大的空間。默認(rèn)的值對于大多數(shù)系統(tǒng)來說都足夠了。如果你加大這個(gè)值，那么Oracle 服務(wù)器將允許從這個(gè)保留列表中更少的分配并且將從共享池列表中請求更多的內(nèi)存。這個(gè)參數(shù)在Oracle 8i 和更高的版本中是隱藏的。提交如下的語句查找這個(gè)參數(shù)值: 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 的一個(gè)新特性叫做 "自動內(nèi)存管理" 允許DBA保留一個(gè)共享內(nèi)存池來分shared pool,buffer cache, java pool 和large pool。一般來說，當(dāng)數(shù)據(jù)庫需要分配一個(gè)大的對象到共享池中并且不能找到連續(xù)的可用空間，將自動使用其他SGA結(jié)構(gòu)的空閑空間來增加共享池的大小 。既然空間分配是Oracle自動管理的，ora-4031出錯(cuò)的可能性將大大降低。自動內(nèi)存管理在初始化參數(shù)SGA_TARGET大于0的時(shí)候被激活。當(dāng)前設(shè)定可以通過查詢v$sga_dynamic_components 視圖獲得。請參考10g管理手冊以得到更多內(nèi)容 。

2.診斷ORA-04031 錯(cuò)誤

注：大多數(shù)的常見的 ORA-4031 的產(chǎn)生都和 SHARED POOL SIZE 有關(guān)，這篇文章中的診斷步驟大多都是關(guān)于共享池的。　對于其它方面如Large_pool或是Java_pool，內(nèi)存分配算法都是相似的，一般來說都是因?yàn)榻Y(jié)構(gòu)不夠大造成。

ORA-04031 可能是因?yàn)?SHARED POOL 不夠大，或是因?yàn)樗槠瑔栴}導(dǎo)致數(shù)據(jù)庫不能找到足夠大的內(nèi)存塊。

ORA-04031 錯(cuò)誤通常是因?yàn)閹旄咚倬彌_中或共享池保留空間中的碎片。 在加大共享池大小的時(shí) 候考慮調(diào)整應(yīng)用,使用共享的SQL 并且調(diào)整如下的參數(shù)：

SHARED_POOL_SIZE,

SHARED_POOL_RESERVED_SIZE,

SHARED_POOL_RESERVED_MIN_ALLOC.

首先判定是否ORA-04031 錯(cuò)誤是由共享池保留空間中的庫高速緩沖的碎片產(chǎn)生的。提交下的查詢：

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 錯(cuò)誤就是因?yàn)楣蚕沓乇Ａ艨臻g缺少連續(xù)空間所致。要解決這個(gè)問題,可以考慮加大SHARED_POOL_RESERVED_MIN_ALLOC 來降低緩沖進(jìn)共 享池保留空間的對象數(shù)目，并增大 SHARED_POOL_RESERVED_SIZE 和 SHARED_POOL_SIZE 來加大共享池保留空間的可用內(nèi)存。

如果：

REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

或者

REQUEST_FAILURES 等于0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

那么是因?yàn)樵趲旄咚倬彌_缺少連續(xù)空間導(dǎo)致ORA-04031 錯(cuò)誤。

第一步應(yīng)該考慮降低SHARED_POOL_RESERVED_MIN_ALLOC 以放入更多的對象到共享池保留空間中并且加大SHARED_POOL_SIZE。

3.解決ORA-04031 錯(cuò)誤

ORACLE BUG

Oracle推薦對你的系統(tǒng)打上最新的PatchSet。大多數(shù)的ORA-04031錯(cuò)誤都和BUG 相關(guān)，可以通過使用這些補(bǔ)丁來避免。

下面表中總結(jié)和和這個(gè)錯(cuò)誤相關(guān)的最常見的BUG、可能的環(huán)境和修補(bǔ)這個(gè)問題的補(bǔ)丁。

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代碼時(shí)出現(xiàn)的ORA-4031

在你編譯Java代碼的時(shí)候如果內(nèi)存溢出，你會看到錯(cuò)誤：

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")

解決辦法是關(guān)閉數(shù)據(jù)庫然后把參數(shù) JAVA_POOL_SIZE 設(shè)定為一個(gè)較大的值。這里錯(cuò)誤信息中提到的 "shared pool" 其實(shí)共享全局區(qū)(SGA)溢出的誤導(dǎo)，并不表示你需要增加SHARED_POOL_SIZE，相反，你必須加大 JAVA_POOL_SIZE 參數(shù)的值，然后重啟動系統(tǒng)，再試一下。參考: <2736601>。

小的共享池尺寸

很多情況下，共享池過小能夠?qū)е翺RA-04031錯(cuò)誤。下面信息有助于你調(diào)整共享池大小：

庫高速緩沖命中率

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

SELECT SUM(PINS) "EXECUTIONS",

SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"

FROM V$LIBRARYCACHE;

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

共享池大小計(jì)算

要計(jì)算最適合你工作負(fù)載的共享池大小，請參考：

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

共享池碎片

每一次，需要被執(zhí)行的SQL 或者PL/SQL 語句的解析形式載入共享池中都需要一塊特定的連續(xù)的空間。數(shù)據(jù)庫要掃描的第一個(gè)資源就是共享池中的空閑可用內(nèi)存。一旦空閑內(nèi)存耗盡，數(shù)據(jù)庫要查找一塊已經(jīng)分配但還沒使用的內(nèi)存準(zhǔn)備重用。如果這樣的確切尺寸的大塊內(nèi)存不可用，就繼續(xù)按照如下標(biāo)準(zhǔn)尋找：

大塊(chunk)大小比請求的大小大

空間是連續(xù)的

大塊內(nèi)存是可用的(而不是正在使用的)

這樣大塊的內(nèi)存被分開，剩余的添加到相應(yīng)的空閑空間列表中。當(dāng)數(shù)據(jù)庫以這種方式操作一段時(shí)間之后，共享池結(jié)構(gòu)就會出現(xiàn)碎片。

當(dāng)共享池存在碎片的問題,分配一片空閑的空間就會花費(fèi)更多的時(shí)間,數(shù)據(jù)庫性能也會下降(整個(gè)操作的過程中，"chunk allocation"被一個(gè)叫做"shared pool latch" 的閂所控制) 或者是出現(xiàn) ORA-04031 錯(cuò)誤errors (在數(shù)據(jù)庫不能找到一個(gè)連續(xù)的空閑內(nèi)存塊的時(shí)候)。

參考 <61623.1>: 可以得到關(guān)于共享池碎片的詳細(xì)討論。

如果SHARED_POOL_SIZE 足夠大，大多數(shù)的 ORA-04031 錯(cuò)誤都是由共享池中的動態(tài)SQL 碎片導(dǎo)致的。可能的原因如下：

非共享的SQL

生成不必要的解析調(diào)用 (軟解析)

沒有使用綁定變量

要減少碎片的產(chǎn)生你需要確定是前面描敘的幾種可能的因素。可以采取如下的一些方法，當(dāng)然不只局限于這幾種: 應(yīng)用調(diào)整、數(shù)據(jù)庫調(diào)整或者實(shí)例參數(shù)調(diào)整。

請參考 <62143.1>，描述了所有的這些細(xì)節(jié)內(nèi)容。這個(gè)注釋還包括了共享池如何工作的細(xì)節(jié)。

下面的視圖有助于你標(biāo)明共享池中非共享的SQL/PLSQL：

V$SQLAREA 視圖

這個(gè)視圖保存了在數(shù)據(jù)庫中執(zhí)行的SQL 語句和PL/SQL 塊的信息。下面的SQL 語句可以顯示給你帶有l(wèi)iteral 的語句或者是帶有綁定變量的語句：

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 后的數(shù)值 "30" 可以根據(jù)需要調(diào)整以得到更為詳細(xì)的信息。

X$KSMLRU 視圖

這個(gè)固定表x$ksmlru 跟蹤共享池中導(dǎo)致其它對象換出(age out)的應(yīng)用。這個(gè)固定表可以用來標(biāo)記是什么導(dǎo)致了大的應(yīng)用。

如果很多對象在共享池中都被階段性的刷新可能導(dǎo)致響應(yīng)時(shí)間問題并且有可能在對象重載入共享池中的時(shí)候?qū)е聨旄咚倬彌_閂競爭問題。

關(guān)于這個(gè)x$ksmlru 表的一個(gè)不尋常的地方就是如果有人從表中選取內(nèi)容這個(gè)表的內(nèi)容就會被擦除。這樣這個(gè)固定表只存儲曾經(jīng)發(fā)生的最大的分配。這個(gè)值在選擇后被重新設(shè)定這樣接下來的大的分配可以被標(biāo)記，即使它們不如先前的分配過的大。因?yàn)檫@樣的重置，在查詢提交后的結(jié)果不可以再次得到，從表中的輸出的結(jié)果應(yīng)該小心的保存。監(jiān)視這個(gè)固定表運(yùn)行如下操作：

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;

這個(gè)表只可以用SYS用戶登錄進(jìn)行查詢。

X$KSMSP 視圖 (類似堆Heapdump信息)

使用這個(gè)視圖能找出當(dāng)前分配的空閑空間，有助于理解共享池碎片的程度。如我們在前面的描述，查找為游標(biāo)分配的足夠的大塊內(nèi)存的第一個(gè)地方是空閑列表( free list)。 下面的語句顯示了空閑列表中的大塊內(nèi)存:

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 錯(cuò)誤與 Large Pool

大池是個(gè)可選的內(nèi)存區(qū)，為以下的操作提供大內(nèi)存分配：

MTS會話內(nèi)存和 Oracle XA 接口

Oracle 備份與恢復(fù)操作和I/O服務(wù)器進(jìn)程用的內(nèi)存(緩沖)

并行執(zhí)行消息緩沖

大池沒有LRU列表。這和共享池中的保留空間不同，保留空間和共享池中其他分配的內(nèi)存使用同樣的LRU列表。大塊內(nèi)存從不會換出大池中，內(nèi)存必須是顯式的被每個(gè)會話分配并釋放。一個(gè)請求如果沒有足夠的內(nèi)存，就會產(chǎn)生類似這樣的一個(gè)ORA-4031錯(cuò)誤：

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

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

這個(gè)錯(cuò)誤發(fā)生時(shí)候可以檢查幾件事情：

1- 使用如下語句檢查 V$SGASTAT ，得知使用和空閑的內(nèi)存： SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool';

2- 你還可以采用 heapdump level 32 來 dump 大池的堆并檢查空閑的大塊內(nèi)存的大小

從大池分配的內(nèi)存如果是LARGE_POOL_MIN_ALLOC 子節(jié)的整塊數(shù)有助于避免碎片。任何請求分配小于LARGE_POOL_MIN_ALLOC 大塊尺寸都將分配LARGE_POOL_MIN_ALLOC的大小。一般來說，你會看到使用大池的時(shí)候相對共享池來說要用到更多的內(nèi)存。通常要解決大池中的ORA-4031錯(cuò)誤必須增加 LARGE_POOL_SIZE 的大小。

5. ORA-04031 和共享池刷新

有一些技巧會提高游標(biāo)的共享能力，從而共享池碎片和ORA-4031都會減少。最佳途徑是調(diào)整應(yīng)用使用綁定變量。另外在應(yīng)用不能調(diào)整的時(shí)候考慮使用CURSOR_SHARING參數(shù)和FORCE不同的值來做到 (要注意那會導(dǎo)致執(zhí)行計(jì)劃改變，所以建議先對應(yīng)用進(jìn)行測試)。當(dāng)上述技巧都不可以用的時(shí)候，并且碎片問題在系統(tǒng)中比較嚴(yán)重，刷新共享持可能有助于減輕碎片問題。但是，必須加以如下考慮：

刷新將導(dǎo)致所有沒被使用的游標(biāo)從共享池刪除。這樣，在共享池刷新之后，大多數(shù)SQL和PL/SQL游標(biāo)必須被硬解析。這將提高CPU的使用，也會加大Latch的活動。

當(dāng)應(yīng)用程序沒有使用綁定變量并被許多用戶進(jìn)行類似的操作的時(shí)候(如在OLTP系統(tǒng)中) ，刷新之后很快還會出現(xiàn)碎片問題。所以共享池對設(shè)計(jì)糟糕的應(yīng)用程序來說不是解決辦法。

對一個(gè)大的共享池刷新可能會導(dǎo)致系統(tǒng)掛起，尤其是實(shí)例繁忙的時(shí)候，推薦在非高峰的時(shí)候刷新

6. ORA-04031錯(cuò)誤的高級分析

如果前述的這些技術(shù)內(nèi)容都不能解決ORA-04031 錯(cuò)誤，可能需要額外的跟蹤信息來得到問題發(fā)生的共享池的快照。

調(diào)整init.ora參數(shù)添加如下的事件得到該問題的跟蹤信息：

event = "4031 trace name errorstack level 3"

event = "4031 trace name HEAPDUMP level 3"

如果問題可重現(xiàn)，該事件可設(shè)定在會話層，在執(zhí)行問題語句之前使用如下的語句： SQL> alter session set events '4031 trace name errorstack level 3';

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

把這個(gè)跟蹤文件發(fā)給Oracle支持人員進(jìn)行排錯(cuò)。

重要標(biāo)注: Oracle 9.2.0.5 和Oracle 10g 版本中，每次在發(fā)生ORA-4031 錯(cuò)誤的時(shí)候會自動創(chuàng)建一個(gè)跟蹤文件，可以在user_dump_dest 目錄中找到。如果你的系統(tǒng)是上述的版本，你不需要再進(jìn)行前面描述中的步驟。