alert日誌遇到 ORA-04030 的分析

alert日誌遇到 ORA-04030 的分析

昨天去一客戶那裡做巡檢，遇到了經典的ora-04030錯誤，alert日誌如下：

Tue Oct 28 09:57:46 2014
Errors in file /Oracle/app/oracle/diag/rdbms/wmsdb/wmsdb/trace/wmsdb_ora_33358038.trc  (incident=177302):
ORA-04030: out of process memory when trying to allocate 118808 bytes (QERHJ hash-joi,kllcqas:kllsltba)
Incident details in: /oracle/app/oracle/diag/rdbms/wmsdb/wmsdb/incident/incdir_177302/wmsdb_ora_33358038_i177302.trc
Errors in file /oracle/app/oracle/diag/rdbms/wmsdb/wmsdb/trace/wmsdb_ora_33358038.trc  (incident=177303):
ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 118808 bytes (QERHJ hash-joi,kllcqas:kllsltba)
Incident details in: /oracle/app/oracle/diag/rdbms/wmsdb/wmsdb/incident/incdir_177303/wmsdb_ora_33358038_i177303.trc
Errors in file /oracle/app/oracle/diag/rdbms/wmsdb/wmsdb/incident/incdir_177302/wmsdb_ora_33358038_i177302.trc:
ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 118808 bytes (QERHJ hash-joi,kllcqas:kllsltba)
Errors in file /oracle/app/oracle/diag/rdbms/wmsdb/wmsdb/trace/wmsdb_ora_33358038.trc  (incident=177304):
ORA-04030: out of process memory when trying to allocate 160 bytes (pga heap,control file cache)
ORA-04030: out of process memory when trying to allocate 118808 bytes (QERHJ hash-joi,kllcqas:kllsltba)
Incident details in: /oracle/app/oracle/diag/rdbms/wmsdb/wmsdb/incident/incdir_177304/wmsdb_ora_33358038_i177304.trc
Tue Oct 28 09:57:53 2014
Trace dumping is performing id=[cdmp_20141028095753]
Tue Oct 28 09:57:54 2014
Sweep Incident[177303]: completed
Tue Oct 28 09:57:57 2014
Errors in file /oracle/app/oracle/diag/rdbms/wmsdb/wmsdb/incident/incdir_177302/wmsdb_ora_33358038_i177302.trc:
ORA-04030: out of process memory when trying to allocate 160 bytes (pga heap,control file cache)
ORA-04030: out of process memory when trying to allocate 118808 bytes (QERHJ hash-joi,kllcqas:kllsltba)
Errors in file /oracle/app/oracle/diag/rdbms/wmsdb/wmsdb/trace/wmsdb_ora_33358038.trc  (incident=177305):
ORA-04030: out of process memory when trying to allocate 16776728 bytes (QERHJ hash-joi,QERHJ Hash Table Entries)
Incident details in: /oracle/app/oracle/diag/rdbms/wmsdb/wmsdb/incident/incdir_177305/wmsdb_ora_33358038_i177305.trc
Trace dumping is performing id=[cdmp_20141028095802]
......

該客戶的資料庫是Oracle 11.1.0.7，運行在p550主機上，16個邏輯CPU，16G記憶體，AIX 6.1 64位系統，共分配了8G記憶體給SGA，2G記憶體給PGA，由memory_target=10G參數動態調整，同時，memory_max_target也設定成10G，表示Oracle最多可以佔用OS的記憶體為10G（實體記憶體的62.5%）。這2個參數是11g新增的，可以動態分配SGA和PGA，而在10g中僅只能通過設定sga_target來動態管理sga中的各記憶體組件，pga是要另外手動設定的。

SQL> show parameters target

NAME                                            TYPE                  VALUE
------------------------------------ ---------------------- ------------------------------
archive_lag_target                            integer                    0
db_flashback_retention_target                integer                    1440
fast_start_io_target                          integer                    0
fast_start_mttr_target                        integer                    0
memory_max_target                            big integer                10G
memory_target                                big integer                10G
pga_aggregate_target                          big integer                2G
sga_target                                    big integer                8G

網上google了一下，很多關於ora-04030的錯誤的描述都大同小異，主要由以下幾種：
A. 對於32 BIT系統，有SGA 1.7G限制
B. 某些OS系統本身也有一些記憶體參數限制
C. OS系統本身實體記憶體+Swap的限制

對於A，由於系統是64 bit的，沒有1.7G的限制；
對於B，用ulimit查看的結果為ulimited;
對於C，OS本身的記憶體為16G，不過用topas查看，記憶體使用量率已經為90%

通常ORA-04030的問題一般是PGA資源過度分派造成的（對應的操作是sort/hash_join）。從9i開始，pga_aggregate_target指定了所有session總共使用的最大PGA上限，這裡為2G。如果該值被設定了則預設的workarea_size_policy=auto, sort_area_size/sort_area_retained_size將被忽略。那麼直接減小pga_aggregate_target就能解決一部分ORA-04030問題。

另外，該錯誤意味著Oracle伺服器處理序無法從作業系統分配更多記憶體。該記憶體由PGA（Program Global Area）組成，其內容取決於伺服器配置。對於專用的伺服器處理序，記憶體包含堆棧以及用於儲存使用者會話資料、遊標資訊和排序區的UGA（User Global Area）。在多線程配置中（共用伺服器），UGA被分配在SGA（System Global Area）中，所以在這種配置下UGA不是造成ORA-4030錯誤的原因。因此，ORA-04030表示進程需要更多記憶體（堆棧 UGA 或 PGA）來執行其任務。

由於發生了這個錯誤，因此無法從作業系統分配記憶體。這個錯誤可能是進程本身導致的，例如進程需要過多的記憶體，或者一些其他原因導致作業系統記憶體被耗盡，例如SGA太大或系統虛擬記憶體（物理Memory + Swap）中要容納的進程過多。許多作業系統會對單個進程能夠擷取的記憶體量加以限制，以便自我保護。

查閱了一篇官方文檔，是這麼說的：

APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and later    --11.2.0.3版本居然也沒有修複這個bug!
Information in this document applies to any platform.
***Checked for currency 24-July-2014***

SYMPTOMS
1.  A session crashes with:
ORA-04030:out of process memory when trying to allocate 4194344 bytes(QERHJ hash-join,QERHJ list array)
2.  Review of the instance parameters set reveals:
"_pga_max_size"=614400KB

CAUSE
The cause of this problem has been identified in:
Bug:13447197 - ORA-04030: OUT OF PROCESS MEMORY WHEN TRYING TO ALLOCATE 262168 BYTES (QERGH HAS
suspended as related to:
unpublished Bug:9506362 - ORA-04030: OUT OF PROCESS MEMORY (QERHJ HASH-JOI,KLLCQAS:KLLSLTBA)
Both bugs have been suspended due to lack of reproducibility.

--這是一個在11g上的bug

SOLUTION
As the bugs have been suspended due to lack of information to perform analysis, the only possible
workaround is to set the _PGA_MAX_SIZE instance parameter to a smaller value.   

--唯一可能的解決方案是設定隱含參數“_PGA_MAX_SIZE”為一個較小的值

This parameter determines the maximum size which can be used for per-process PGA memory. The default value
is 200MB and the range of valid values is from 10MB up to 4TB-1.

--這個參數可以指定為每一個進程分配地PGA記憶體，預設值為200M，合法的值為10M~(4T-1)

The per-process PGA memory can be limited by setting the _PGA_MAX_SIZE to a smaller value which internally
forces the hash-join to use the less memory and avoid the ORA-4030, like in:
SQL> alter system set "_pga_max_size"=100M;

--用“_PGA_MAX_SIZE”強制hash-join用更少的記憶體，來避免ora-04030，如設定為100M。

有關避免此錯誤的一般建議

1.一些操作需要大量的記憶體，對於排序問題，減少SORT_AREA_SIZE會有所協助。Oracle伺服器處理序會將PGA中的SORT_AREA_SIZE位元組分配給排序操作。如果完成搜尋需要更多記憶體，伺服器處理序將會使用temporary segment。這意味著減少SORT_AREA_SIZE會對需要大量排序操作的查詢效能產生影響。
2.對於9i及更高版本，通過將參數WORKAREA_SIZE_POLICY設定為AUTO，以及在初始設定檔案中指定PGA_AGGREGATE_TARGET的大小，即可啟用自動SQL執行記憶體管理功能。使用自動PGA記憶體管理將有助於減少發生ORA-04030錯誤的可能性。注意，OpenVMS作業系統在Oracle 9i版本上不支援PGA_AGGREGATE_TARGET，但是在Oracle 10g版本上是支援的。
3.PL/SQL程式也可分配大量記憶體，因此可能需要重寫應用程式的某些部分。儘管PL/SQL表非常容易使用，但它確實需要在PGA中分配記憶體。
4.查看optimizer策略，一些訪問路徑可能會因排序操作、較多行上的函數使用等原因而需要更多記憶體。
5.在某些作業系統上（例如 Microsoft Windows），可能要降低 SGA 的大小以便於PGA獲得更大的記憶體。
6.確保作業系統和Oracle的記憶體限制設定合理。
7.確保有足夠的可用記憶體（物理Memory和Swap）。

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2