轉貼：ORA-04031故障分析處理

當任何試圖分配一個大的連續的共享池中的記憶體失敗，Oracle首先會重新整理掉共享池中所有目前不在使用的物件，然後使得記憶體塊被合併在一起。如果記憶體塊仍然不夠滿足需求，那麼就會出現ORA- 04031錯誤。這些錯誤同樣可能發生在ASM的例項中。預設的共享池的大小基本能夠滿足大部分的環境，但是如果遇到ORA-04031錯誤的時候可能就需要增大。 

當遇到這個錯誤的時候，往往會出現如下的訊息顯示： 

04031, 00000, "unable to allocate %s bytes of shared memory ("%s","%s", "%s","%s")" 

先來了解一下跟共享池相關的例項引數： 

a) SHARED_POOL_SIZE --– 這個引數指定共享池的大小，以位元組為單位。 

b) SHARED_POOL_RESERVED_SIZE --– 指定為大的連續的共享池記憶體請求預留的共享池的大小。這個引數連同 SHARED_POOL_RESERVED_MIN_ALLOC引數，可以用於避免消除由於共享池碎片太多導致迫使Oracle搜尋空閒的共享池空間而產生ORA-04031錯誤。 

c) _SHARED_POOL_RESERVED_MIN_ALLOC --– 這個引數用於控制預留記憶體的分配。大於這個引數的記憶體分配可以使用預留記憶體的列表。這個引數的預設值適合 絕大多數的系統，如果增加這個引數值，那麼Oracle將允許分配越少的預留記憶體列表而更多的請求共享池列表。這個引數在Oracle8i和之後的版本都是隱含引數，但是可以透過執行如下SQL查到： 

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; 

對於Oracle10g， 具有一個新特性自動記憶體管理，允許dba保留用於分配給Oracle記憶體區域使用的共享記憶體。通常來說，當Oracle需要分配一個大物件到共享池中卻無 法找到 一段連續可用的記憶體空間時候，Oracle10g將會從其他SGA結構中使用自由記憶體來自動增大共享池的大小。自從10g資料庫中記憶體空間分配被 Oracle自動管理之後，出現ORA-04013錯誤的可能性大大降低了，當SGA_TARGET引數大於0的時候，就啟用了自動記憶體管理特性，當前 SGA的設定可以查詢檢視v$sga_dynamic_components。 

現在可以開始診斷ORA-04013錯誤了。 

大部分的ORA- 4031錯誤都和共享池大小有關，因此我們診斷這個錯誤一般都由共享池開始。當然，large_pool和java_pool的記憶體分配機制比較的簡單， 一般錯誤都是由於不夠大引起的。由於設定共享池大小不合理或者是共享池碎片太多都會導致Oracle無法找到一個足夠大的記憶體段來容納資料物件從而產生 ORA-04013錯誤。已經有很多的例子說明對於ASM例項，預設的共享池大小是遠遠不夠的，如果 對於一個ASM例項出現這個錯誤，首先應該檢視共享池大小，如果太小了，增大到不出現錯誤為止。不適當的大小：首先決定出現ORA-04031錯誤的的原因是在library cache中缺乏連續的記憶體空間，可以透過查詢檢視v$SHARED_POOL_RESERVED，滿足如下的條件： 

REQUEST_FAILURES > 0 並且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC  

REQUEST_FAILURES=0 並且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC 

如果出現上述情況，可以認為是由於設定了較小的SHARED_POOL_RESERVED_MIN_ALLOC使得資料庫把更多的物件放到共享池保留空間中，增大共享池大小可以解決這種問題的出現。但是如果當使用了多個池的時候, LAST_FAILURE_SIZE會顯示錯誤,會顯示出所有池失敗大小的總和,這個是個bug（3669074）,在Oracle的9.2.0.7,10.1.0.4和10.2.x版本中被修正。 

共享池碎片：如果是由於共享池碎片引起的ORA-04031錯誤，那麼需要判斷是由於library cache的碎片導致的還是共享池保留空間中的碎片太多導致的，可以用下面的規則來判定： 

REQUEST_FAILURES > 0  

並且LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC 

要解決這個問題，增大SHARED_POOL_RESERVED_MIN_ALLOC來減少物件被快取到共享池保留空間中的數量並且增大SHARED_POOL_RESERVED_SIZE 和SHARED_POOL_SIZE來提高共享池保留空間中有更多可用的記憶體。 

現在看看如何來解決ORA-04031的錯誤。 

Oracle BUGs：Oracle強烈建議安裝最新的資料庫補丁，大部分的ORA-04031錯誤都和BUGs相關，安裝最新的補丁可以避免這些錯誤。例如Bug 1397603, Bug 1640583,Bug 2104071,Bug 3910149當編譯Java程式碼的時候出現ORA-04031錯誤：當編譯java程式碼（包括loadjava或者deployjb）,記憶體消耗完全之後也會出現這個錯誤： 

ORA-04031: unable to allocate bytes of shared memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal") 

針對這種錯誤的解決方案是關閉資料庫，並且設定更大的JAVA_POOL_SIZE大小，錯誤中提及的共享池很容易誤導認為是SGA記憶體消耗完全，此時不能去增大共享池大小，而應該是增大JAVA池的大小，然後重啟資料庫繼續嘗試。 

過於小的共享池： 在大多的情況下，共享池過小能導致ORA-04031錯誤。下面兩方面的資訊可以幫助我們來調整共享池的大小：  

Library Cache Hit Ratio：命中率可以幫助估算共享池的使用率，透過一個SQL/PLSQL語句需要被分析而不是被重複使用的次數，下面的SQL語句可以用來計算library cache命中率：  

SELECT SUM(PINS) "EXECUTIONS",  

SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"  

FROM V$LIBRARYCACHE;  

如果執行的未命中率多於1%，那麼就很有必要增大共享池大小了。 

共享池的碎片：每次一個SQL或者PL/SQL需要被分析意味著載入library cache中的時候需要一定數量的連續自由空間，當自由空間被消耗盡，資料庫會尋找重新使用一個已經分配過的但卻又不是正在使用的空間。如果需要空間大小不確定，尋找連續自由空間將基於下列的標準： 

a) 塊大小大於需要的大小  

b) 塊空間是連續的 

c) 塊當前沒有在使用中 

那麼這個塊將被分割，並且剩餘的自由空間被加入到正確的自由空間列表中。當資料庫這麼執行一段時期之後，共享池結構就被碎片化了。 當共享池中碎片情況比較嚴重的時候，就會引起的ORA-04031錯誤，因此，以後每次分配自由空間的時間將會花費更多的時間（由共享池的latch來控制），導致整個資料庫效能下降。 

如果共享池本身已經設定的足夠大，那麼出現的大部分ORA-04031錯誤都是由於共享池中的動態SQL碎片引起的。 

a) 沒有共享的SQL  

b) 執行了沒有必要的軟分析呼叫  

c) 沒有使用繫結變數  

為了減少碎片的產生，那就必須注意前面引起碎片的三點原因，通常我們必須去分析應用是如何來使用共享池的以便能夠最大化使用共享遊標。 

下面的幾個檢視可以幫助更好的找出在共享池中沒有共享的SQL/PLSQL： 

V$SQLAREA檢視：  

這個檢視儲存了每個SQL和PL/SQL執行的塊，下面的SQL能夠幫助找到有問題的語句： 

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;  

注：上面的30可以根據需要隨便調整，這裡只是個例子。 

X$KSMLRU檢視：  

這個檢視記錄所有在共享池中由於分配自由空間而導致其他物件被重新整理出共享池的資訊。通常用來判定什麼導致了大的自由空間分配。如果很多物件週期性的被重新整理出共享池，那麼會導致響應時間問題，例如libaray cache latch的競爭問題。 

尤其需要注意的是當儲存了最大的自由空間分配之後，任何時候查詢這個檢視都會刪除這個檢視中的內容，即使下一個分配的大的自由空間沒有以前的大，這些被查詢的值都會被重置，因此，查詢這個表的結果應該被小心的儲存下來。 

監控這個檢視只需要執行下面的SQL：(SYS使用者) 

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;  

 

 

 

X$KSMSP檢視 ：  

這個檢視能夠幫助找出當前的自由空間是如何分配的，能夠更好的理解共享池碎片的程度。下面這個SQL能夠找到所有在free list上的可用塊： 

select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 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, KSMCHIDX, 10*trunc(KSMCHSIZ/10)  

UNION ALL  

select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,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, KSMCHIDX, 20*trunc(KSMCHSIZ/20)  

UNION ALL  

select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 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, KSMCHIDX, 50*trunc(KSMCHSIZ/50)  

UNION ALL  

select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 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, KSMCHIDX, 500*trunc(KSMCHSIZ/500)  

UNION ALL  

select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 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, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);  

注：這個檢視查詢到的資訊是使用HEAPDUMP level 2產生的資訊的一部分。注意不要太頻繁的執行這個SQL，容易導致共享池的其他記憶體問題出現。 

如果上述查詢的結果顯示大部分的可用空間在列表上，那麼產生ORA-04031的錯誤很可能就是由於共享池嚴重的碎片引起的。 

這個檢視還可以用來檢視在SGA中的全部記憶體使用情況： 

SELECT KSMCHCLS CLASS,  

COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,  

To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"  

FROM X$KSMSP  

GROUP BY KSMCHCLS;  

KSMCHCLS值的說明： 

R-free ：保留列表  

R-freea：保留列表  

Free：自由記憶體 

freeabl ： 使用者使用的記憶體/系統處理  

perm ：分配給系統的記憶體  

recr ：使用者使用的記憶體/系統處理 

a) 如果自由記憶體的大小過小（少於5M左右），那麼應該增大shared_pool_size和 shared_pool_reserved_size的大小。  

b) 如果perm連續增長，那麼可能是出現了記憶體洩露。  

c) 如果freeabl和recr一直都很大，那麼意味著有很多的沒有釋放的遊標資訊儲存。  

d) 如果free非常大但是仍然出現ORA-04031錯誤，那麼可以和共享池碎片聯絡起來處理。 

ORA-04031錯誤和大池（Large Pool） 

大池是一個可選的記憶體區域，能夠為下列操作提供大記憶體分配： 

a) 針對多執行緒伺服器和OracleXA介面的會話記憶體。 

b) 針對Oracle備份和恢復操作所需要的記憶體。 

c) 並行執行的資訊快取。 

大池沒有LRU列表，它和共享池中的保留空間不太一樣，大池中的記憶體塊永遠不會重新整理出去，針對每一個會話明確的分配和釋放記憶體。 

如果大池中沒有自由記憶體空間，而又有請求，那麼就會出現類似下面的ORA-04031錯誤。 

ORA-04031: unable to allocate XXXX bytes of shared memory ("large pool","unknown object","session heap","frame") 

當出現上述錯誤的時候，可以檢視如下幾個方面： 

a) 檢查V$SGASTAT看看多少的記憶體被使用和空閒： 

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

b) 使用heapdump level 32來dump出大池heap，確定自由的塊大小。 

從大池中分配的記憶體如果是LARGE_POOL_MIN_ALLOC 的塊數倍可以避免產生碎片，任何請求要求分配的塊大小如果小於LARGE_POOL_MIN_ALLOC，那麼將分配 LARGE_POOL_MIN_ALLOC大小的記憶體。通常來說如果大池出現ORA-04031錯誤，增大LARGE_POOL_SIZE的大小都有助於 消除這個錯誤。  

針對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 536870915';