【DB筆試面試528】在Oracle中，如何解決ORA-04030和ORA-04031錯誤？

• 2019 年 10 月 10 日
• 筆記

♣

題目部分

在Oracle中，如何解決ORA-04030和ORA-04031錯誤？

♣

答案部分

ORA-04030和ORA-04031都是典型的記憶體分配錯誤，下面分別講解。

（一）ORA-04030錯誤

ORA-04030報錯形如「ORA-04030 'out of process memory when trying to allocate %s bytes (%s,%s)'」，該錯誤意味著Oracle Server進程無法從作業系統分配更多記憶體。該記憶體由PGA組成，其內容取決於伺服器配置。對於專用的伺服器進程，記憶體包含堆棧以及用於保存用戶會話數據、游標資訊和排序區的UGA。在多執行緒伺服器（共享伺服器）中，UGA被分配在SGA中，所以在這種配置下UGA不是造成ORA-04030錯誤的原因。因此，ORA-04030表示進程需要更多記憶體（堆棧、UGA或PGA）來執行其任務。

ORA-04030錯誤可能是進程本身導致的，例如進程需要過多的記憶體，或者一些其它原因導致作業系統記憶體被耗盡，例如，SGA分配太大或系統虛擬記憶體（物理記憶體+交換空間）中要容納的進程過多。許多作業系統會對單個進程能夠獲取的記憶體量加以限制，以便自我保護。在大部分情況下，發生ORA-04030錯誤的進程並非總是記憶體損耗的元兇，錯誤的發生僅僅是因為此進程無法取得所需的記憶體造成的。

MOS文檔（ID 1548826.1和199746.1）對該錯誤有非常詳細的說明。如果發生了ORA-04030錯誤，那麼會在告警日誌中記錄詳細資訊。若發生ORA-04030錯誤，則可以從以下幾個方面去排查該錯誤：

①　是否仍然有足夠的可用記憶體？

主要使用作業系統特定的工具（top、topas、vmstat、swapon -s）來檢查記憶體使用情況。如果有足夠的記憶體可用，那麼就需要檢查作業系統是否存在強制限制。如果記憶體已被耗盡，那麼就需要找出記憶體被用到了哪些地方。

②　是否設置了作業系統限制？

如果仍有足夠的記憶體可用，那麼有可能是進程需要使用的記憶體量是不被允許的。使用命令「ulimit -a」查看作業系統限制。尤其對於「data seg size」選項，應該設置為unlimited。在RAC或GRID環境中，由於資料庫監聽是通過CRS進行啟動，所以監聽繼承了root用戶的ulimit限制。如果在root的ulimit限制中data(kbytes)的限制為1310kb，那麼表示每個通過監聽連接的進程能分配的記憶體資源不能超過1310kb。

③　是否設置了Oracle限制？

查詢參數PGA_AGGREGATE_TARGET的大小，該參數限制一個實例可以分配的PGA總量。以下查詢用於查找分配給所有會話的PGA區的記憶體總量：

SELECT SUM(VALUE) / 1024 / 1024  PGA_SIZE_MB    FROM V$SESSTAT S, V$STATNAME N   WHERE N.STATISTIC# = S.STATISTIC#     AND NAME = 'session pga memory';  

④　哪個進程需要的記憶體過多？

一些操作會需要大量的進程記憶體，例如，大型的PL/SQL表或大量的排序操作。在這些情況下，在出現錯誤ORA-04030之前，進程將會運行一段時間，所以，可以在這段時間內找出記憶體分配的位置和原因。可以使用以下查詢來查找所有Oracle進程的PGA和UGA大小：

SELECT SID, NAME, VALUE    FROM V$STATNAME N, V$SESSTAT S   WHERE N.STATISTIC# = S.STATISTIC#     AND NAME LIKE 'session%memory%'   ORDER BY 3 ASC;  

通常，從作業系統的角度來確認進程記憶體使用情況，是一個好辦法。畢竟，使用過多記憶體的不一定是Oracle Server進程。

⑤　如何收集有關進程實際正在執行的任務的資訊？

l可以做heapdump，然後分析dump結果：

SQL> select PID from v$process p, v$session s where p.addr=s.paddr and sid=<SID>;  SQL> oradebug setorapid <PID>  SQL> oradebug unlimit  SQL> oradebug dump errorstack 3  SQL> oradebug dump heapdump 536870917  SQL> oradebug tracefile_name (shows the path and filename information)  SQL> oradebug close_trace  

或者使用event，如下所示：

設置系統級事件來產生ORA-04030錯誤時的trace文件，設置event如下：

alter system set events '4030 trace name errorstack level 3;name HEAPDUMP level 536870917';  

得到報錯的dump文件後，關閉生成event：

alter system set events '4030 trace name errorstack off';  alter system set events '4030 trace name HEAPDUMP off';  

（二）ORA-04031錯誤

SGA中的記憶體池由不同大小的記憶體塊組成。當資料庫實例啟動時，大量的記憶體塊被分配到不同的池中並且由空閑列表哈希Bucket追蹤。隨著時間推移，由於記憶體塊被分配和回收，記憶體塊會根據它們的大小在池中的不同空閑列表Bucket中移動。

當Oracle不能找到一個足夠大的記憶體塊來滿足用戶操作所帶來的內部分配請求的時候，ORA-04031錯誤就可能在SGA的任何一個池中（Large Pool、Java Pool、Streams Pool（10g新增）、Shared Pool）出現。ORA-04031錯誤資訊會指出哪個池出了問題。如果錯誤指出問題不在共享池中，那麼這通常意味著對應用環境來說，出問題的池配置的太小了。可以將出問題的池增大，然後繼續觀察後續的問題。如果使用Oracle 10g的ASMM功能，那麼MMAN進程會隨著時間推移，嘗試根據記憶體需要收縮或者增大SGA中不同組件的大小。如果在Large Pool、Streams Pool或者Java Pool中遇到了ORA-04031錯誤，那麼可以通過增大SGA_TARGET，使MMAN可以管理更多的記憶體。

MOS文檔（ID 2016002.1和146599.1）對ORA-04031有非常詳細的說明。

在Oracle 9i和之後的版本，共享池可以被劃分為子池。每個子池是一個小號的共享池，有它自己的空閑列表，記憶體結構條目和LRU列表。這是一個對共享池和大池的可擴展性的改變，現在每一個子池都由一個Child Latch來保護，因此可以增加這些池的吞吐量。這意味著不再有之前版本的對於共享池和大池的單獨Latch的競爭。共享池中的保留區域也被平均的劃分到每個子池中。

在Oracle 9.2.0.5或者更高版本中，當發生ORA-04031錯誤時，會生成一個trace文件（通過_4031_dump_bitvec參數控制）。ORA-04031的報錯形式如下所示：

ORA-04031:unable to allocate 4192 bytes of shared memory("shared pool","SELECT/*+FIRST_ROWS*/*F...","sql area(6,0)","kafco:qkacol")  

在這個例子中，問題發生在共享池。錯誤消息也包含記憶體請求失敗的大小的資訊。在這裡，請求SQL Area中4192byte時失敗，並且發生在第6個子池中。

子池的個數跟以下3個方面相關：

①　CPU的個數 系統中每4個CPU可以分配一個子池，最多分配7個子池，由參數CPU_COUNT控制。

②　參數SHARED_POOL_SIZE大小 若ASMM或AMM沒有啟用（即手動管理記憶體，SGA_TARGET和MEMORY_TARGET都沒有設置），則在子池的記憶體分配原則如下所示：

若啟用了ASMM或AMM，參數SHARED_POOL_SIZE被顯式的設置了值，則使用上表的分配原則。如果沒有顯式的設置該參數的值，那麼SHARED_POOL_SIZE的值取SGA_TARGET的50%。如果使用的是AMM，則SGA_TARGET的值取MEMORY_TARGET的60%。

③　隱含參數「_KGHDSIDX_COUNT」的大小，默認為1，表示1個子池。設置的SQL命令為：「ALTER SYSTEM SET "_KGHDSIDX_COUNT"=2 SCOPE=SPFILE;」。

在以上規則中，若設置了隱含參數「_KGHDSIDX_COUNT」為大於1的值，則以該參數為準，否則取①和②中的最小值。例如，如果在Oracle 11g中，有16個CPU，MEMORY_TARGET為4.2G，SGA_TARGET和SHARED_POOL_SIZE的值都為0，那麼16個CPU可以分配4個子池，SHARED_POOL_SIZE的值為1.26G（4.2*0.6*0.5），可以分配2個子池，每個子池為630MB。由於沒有設置隱含參數「_KGHDSIDX_COUNT」，所以，該系統的子池個數為MIN(4,2)=2。

子池的創建是在啟動過程中SGA創建時發生的，所以修改隱含參數「_KGHDSIDX_COUNT」、SHARED_POOL_SIZE和CPU_COUNT之後，需要重新啟動資料庫，子池的數量才能變動。

具體的子池分配、記憶體情況及剩餘記憶體情況可以使用如下的SQL語句查詢：

SELECT 'shared pool (' ||NVL(DECODE(TO_CHAR(KSMDSIDX), '0', '0 - Unused', KSMDSIDX), 'Total') || '):' SUBPOOL,         ROUND(SUM(KSMSSLEN) / 1048576, 2) size_all_MB,         ROUND(sum(CASE WHEN LOWER(KSMSSNAM) LIKE LOWER('%free memory%') THEN KSMSSLEN ELSE 0 END)/ 1048576, 2) size_free_mb    FROM X$KSMSS   WHERE KSMSSLEN > 0   GROUP BY ROLLUP(KSMDSIDX)   ORDER BY SUBPOOL ASC;      --通過以下查詢可以詳細列舉不同子池的Free記憶體塊情況：  set linesize 1000  col sga_heap format a15  col size format a10  SELECT KSMCHIDX SubPool,         'sga heap(' || KSMCHIDX || ',0)' SGA_HEAP,         KSMCHCOM CHUNKCOMMENT ,         decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K') "size",          COUNT(*),         KSMCHCLS STATUS,         SUM(KSMCHSIZ) BYTES,         ROUND(SUM(KSMCHSIZ) / 1048576, 2) MB    FROM X$KSMSP d   WHERE KSMCHCOM = 'free memory'   GROUP BY KSMCHIDX,            KSMCHCLS,            'sga heap(' || KSMCHIDX || ',0)',            KSMCHCOM,            KSMCHCLS ,            decode(round(ksmchsiz/1000),0,'0-1K',1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K')   ORDER BY  SubPool  ;  

需要注意的是，在ASM環境中也用到了共享池。在Oracle 10gR1版本的ASM實例上有ORA-04031的問題，原因是默認值太小了，不能適應磁碟組管理活動的需要。

當試圖在共享池中分配大塊連續記憶體而失敗時，Oracle會首先從池中清理當前不用的對象從而使得空閑記憶體碎片（chunk：記憶體塊）得以合併。如果這樣仍然沒有足夠大的單個chunk來滿足分配需要，那麼會產生ORA-04031報錯。有許多ORA-04031錯誤直接原因都是由於共享池的大小或調整不當造成的。

在日常維護上可以查詢視圖V$SHARED_POOL_RESERVED，當REQUEST_FAILURES>0時，則說明Shared Pool在記憶體分配上存在一定的問題。

如果問題可以重現，那麼可在執行有問題的SQL語句前，在會話級別對事件進行設置：

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

從Oracle 9.2.0.5版本開始，除了在請求heapdump時使用1、2、3或32等級，還可以使用相同等級並加值536870912，這樣將會在此等級上再進一步顯示5個最大的subheaps同時每個subheap下顯示相關5個最大的heap areas。

下面給出分析ORA-04031產生的TRACE文件的一般步驟：

（1）首先需要確認報錯的是SGA的哪個池、哪個子池、需要分配哪部分記憶體、需要分配多大記憶體等問題

大部分資訊可以從報錯資訊直接獲取到。檢查告警日誌並查看錯誤是否記錄，但是，不是所有的ORA-04031錯誤都會記錄在告警日誌中。如果錯誤被記錄，請檢查是SGA的哪部分收到此錯誤，共享池，大池，Java池或Streams池。找到發生ORA-04031錯誤時的trace文件。

（2）在trace文件中搜索關鍵字「Memory Utilization of Subpool」（子池，可以7直接搜索Subpool）和granule size（粒度大小），確認子池的個數及粒度的大小等參數。

（3）找到各個Subpool的使用情況

從Subpool部分分析哪個組件用的記憶體最多，free memory還剩多少記憶體？可能有的子池的free memory已經為0了。可以從視圖V$SGASTAT來檢查是否有組件表現出非正常增長，查詢SQL可以為「SELECT * FROM V$SGASTAT A WHERE A.NAME='obj stat memo';」。如果V$SGASTAT中沒有記錄，那麼也可以查詢DBA_HIST_SGASTAT視圖。

l 如果用的最多的是SQLA（SQLAREA），那麼很可能就是沒有使用綁定變數。

l 如果是不常見的組件（例如，obj stat memo），那麼很可能就是BUG。如果是BUG，那麼可以拿關鍵字去MOS上進行搜索，基本上可以找到相關內容。

在產生的TRACE文件中還需要關注「LIBRARY CACHE STATISTICS」，它代表庫快取的資訊，如下：

LIBRARY CACHE STATISTICS:  (emphasis added on key areas)  namespace           gets hit ratio      pins hit ratio    reloads   invalids  -------------- --------- --------- --------- --------- ---------- ----------  CRSR          1201146935     0.999 4127355897    0.997     977858     467750  TABL             9017452     0.998 680339529     1.000      98991          0  BODY            45900052     1.000 102763403     1.000        428          0  TRGR              127502     0.999   1661141     1.000        194          0  INDX              880913     0.990   1846250     0.978      20895          0  CLST               15560     0.997     32730     0.997         58          0  KGLT                   0     0.000         0     0.000          0          0  PIPE                   0     0.000         0     0.000          0          0  LOB                    0     0.000         0     0.000          0          0  

在Library Cache統計資訊里，找到「hit ratio」百分比，這指示了碎片問題。目標是使得「hit ratio」儘可能的接近100%。另外要查看reloads和invalids資訊。reloads和invalids很多意味著庫快取中發生了很多記憶體清理，可能意味著應用低效和碎片化。

在AWR中，可以查看「Load Profile」、「Shared Pool Advisory」和「Library Cache Activity」等部分進行分別分析。

可以通過如下的SQL語句分析ORA-04031出現的次數和需要分配的位元組數：

SELECT INDX,KGHLURCR,KGHLUTRN,KGHLUFSH,KGHLUOPS,KGHLUNFU,KGHLUNFS FROM  SYS.X$KGHLU WHERE INST_ID = userenv('Instance');        INDX   KGHLURCR   KGHLUTRN   KGHLUFSH   KGHLUOPS   KGHLUNFU   KGHLUNFS  ---------- ---------- ---------- ---------- ---------- ---------- ----------           0       1233      30360      57700     518988        114       4096  

其中，KGHLUNFU表示出現ORA-04031的次數，這裡指出現了114次的4031錯誤。KGHLUNFS表示最後一次出現ORA-04031時，申請的分配大小，在以上結果中表示最後申請共享池的大小是4096位元組。

綜上所述，導致ORA-04031錯誤的原因有如下幾點：

（1）配置問題，表現為某些池配置過小或配置錯誤。解決辦法為增大相應的池（例如，Shared Pool）大小及使用AMM方式來管理記憶體。如果報錯的是Large Pool或Java Pool，那麼可以簡單的增大參數LARGE_POOL_SIZE和JAVA_POOL_SIZE即可。

（2）記憶體中存在大量碎片，導致在分配記憶體的時候沒有連續的記憶體可供分配從而導致ORA-04031錯誤。解決辦法為刷新共享池（ALTER SYSTEM FLUSH BUFFER_CACHE;），該操作在生產庫上要慎用。也可以重啟資料庫，重啟資料庫會釋放記憶體，還會清理記憶體碎片。

（3）應用問題，主要表現在①沒有使用綁定變數，不使用綁定變數會導致庫快取的過度使用。②有多個子游標，每個子游標都會在共享池中分配空間。③高解析率，可能由於使用了動態PL/SQL或在高負載階段執行DDL語句。每次DDL語句執行，都會導致所有引用了這個對象的語句失效。下次執行引用了這個對象的SQL語句時，則不得不重新解析並載入到共享池中。④過度的打開CURSOR而不關閉，一般會導致Shared Pool中的ORA-04031錯誤。⑤SESSION_CACHED_CURSORS、OPEN_CURSOR設置過高。OPEN_CURSOR如果設置的過大，那麼會導致Library Cache中很多對象都處於pin狀態，而不能釋放，那麼當申請Shared Pool記憶體時，通過LRU依然不能找到可用空間，就會導致ORA-04031錯誤。遇見這種情況可以適當減少OPEN_CURSOR的值。

（4）Subpool的不均衡使用。Subpool的不均衡使用是使用Subpool一個缺點之一，對於這種情況可以設置隱含參數「_ENABLE_SHARED_POOL_DURATIONS＝FALSE」來改變Shared Pool的Subpool記憶體結構的分配方式，或者完全使用AMM。

（5）Oracle的BUG導致記憶體泄露。例如，在一些版本中查詢V$SEGMENT_STATISTICS這樣的視圖導致記憶體泄露，使Shared Pool記憶體耗光。同樣的情形還有類似於「obj stat memory」、「gcs resources」、「ges resources」等。這類記憶體通常是在分配時就確定了固定的用途，不能用於其它用途，因此極容易產生碎片。

有關ORA-04030錯誤的更多內容可以參考：http://blog.itpub.net/26736162/viewspace-2138387/。有關ORA-04031錯誤的更多內容可以參考：http://blog.itpub.net/26736162/viewspace-2138388/。

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。