【ASK_ORACLE】ORA-04030診斷方法及預防措施

為什麼會產生ORA-04030？

從ORA-04030的報錯資訊（ORA-04030: out of process memory when trying to allocate xxx bytes (xxx,xxx)）可以推理出：是由於Oracle程式無法從作業系統分配到更多的記憶體。但這是什麼樣的記憶體呢？該記憶體由PGA組成，記憶體包含堆疊以及用於儲存使用者會話資料、遊標資訊和排序區的UGA。因此，ORA-04030表示程式需要更多記憶體（UGA或PGA）。

還有一些其他原因導致作業系統記憶體被耗盡，例如SGA太大或系統虛擬記憶體（實體記憶體和Swap）中包含的程式過多。許多作業系統會對單個程式能夠獲取的記憶體量加以限制來進行自我保護。

 

排查措施

1. 檢查是否仍然有足夠的可用記憶體？

檢查命令：top，vmstat，swapon -s

如果有足夠的記憶體可用，需要再檢查作業系統是否存在強制限制。如果記憶體已被耗盡，就需要找出記憶體被用到了哪些地方。

注：

top：通常會顯示實體記憶體和交換空間的統計資訊

swapon -s：顯示交換空間使用情況

vmstat：顯示可用實體記憶體

 

2. 檢查是否設定了作業系統限制？

如果仍剩餘大量的虛擬記憶體，那麼有可能是我們需要使用的記憶體量是被作業系統限制了。可使用ulimite -a檢視。有的問題可能是因為限制設定得過低造成的，所以需要進行相應的增加。也有的問題可能是由於我們需要使用的太多造成的。但是 顯示“unlimited”的不一定意味著不受限制，實際也可能是基於歷史原因的限制，例如“max memory size”顯示“unlimited”但是實際OS最大記憶體只有4GB。

 

3. 檢查是否被Oracle配置PGA的引數限制？

（1）首先檢視PGA的引數大小

SQL> show parameter PGA_AGGREGATE_TARGET

（2）檢視PGA的實際使用率

SQL> select sum(value)/1024/1024 Mb from v$sesstat s, v$statname n where n.STATISTIC# = s.STATISTIC# and name = 'session pga memory';

 

4. 檢查哪個程式需要的記憶體過多？

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

SQL> 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;

在結合排查步驟1的方法來綜合判斷到底是Oracle本身消耗過多作業系統的記憶體，還是其他軟體程式消耗了過多記憶體。

注：

使用命令“ps -AF --sort resident”可列出具有最大駐留集大小的所有程式。

 

5. 如何找到記憶體消耗最大的SQL的內容？

如果確定了是Oracle的SQL程式佔用過多記憶體，可以查出該SQL所對應的具體內容：

SQL> select sql_text from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid =<SID>;

 

避免ora-04030的一些建議

1. 一些操作需要大量的記憶體。對於排序問題，如果排序少，可以減少SORT_AREA_SIZE引數大小。因為Oracle程式會將PGA中的SORT_AREA_SIZE位元組分配給排序操作。如果完成搜尋需要更多記憶體，伺服器程式將會使用臨時表空間。所以， 減少SORT_AREA_SIZE會對需要大量排序操作的查詢效能產生影響。

2. Oracle 9i及更高版本，透過將引數WORKAREA_SIZE_POLICY設定為“AUTO”，以及在初始化檔案中指定PGA_AGGREGATE_TARGET的大小，即可啟用自動SQL執行記憶體管理功能。使用PGA的自動記憶體管理將有助於減少發生ORA-04030錯誤機率。

3. PL/SQL程式也會佔用大量PGA記憶體，因此可能需要重新最佳化應用程式的某些部分程式碼。

4. 檢視optimizer策略，一些訪問路徑可能會因排序操作、較多行上的函式使用等原因而需要使用更多記憶體。

5. 在某些作業系統上（例如 Microsoft Windows），可能要降低SGA的大小可以讓PGA獲得更大的記憶體。

6. 確保作業系統和Oracle引數設定合理。

7. 確保有足夠的可用記憶體（實體記憶體和Swap）。