Oracle 11.2.0.3 Database for AIX bug導致ORA-04030的報錯

    根據我以往的經驗，在AIX平臺Oracle Database資料庫較其他平臺更容易報ORA-04030的錯誤，PGA的引數有時候設定小了反而不報錯誤。幫助客戶將一個Oracle 11.2.0.3 Restart Database資料庫的伺服器更換到一臺新採購的伺服器，新伺服器分割槽的記憶體要比原有伺服器的記憶體少差不多一半，進行多次Oracle資料庫例項初始化記憶體引數調整之後，在執行同一條SQL語句時依然會報ORA-04030的錯誤：
2014-11-03 22:50:44.074000 +08:00
Errors in file /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/incident/incdir_48299/ynsyn13_ora_26149036_i48299.trc:
ORA-04030: 69040  (pga heap,kgh stack) ?                                         ORA-04030: 15224  (QERHJ hash-joi,kllcqas:kllsltba) ?                                                                                               Errors in file /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/trace/ynsyn13_ora_26149036.trc  (incident=48301):
ORA-04030: 24504  (pga heap,kco buffer) ?                                          ORA-04030: 15224  (QERHJ hash-joi,kllcqas:kllsltba) ?                                                                                                Incident details in: /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/incident/incdir_48301/ynsyn13_ora_26149036_i48301.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Dumping diagnostic data in directory=[cdmp_20141103225044], requested by (instance=1, osid=26149036), summary=[incident=48300].
Sweep [inc][48301]: completed
Sweep [inc][48300]: completed

於是在MOS上進行了一番搜尋，找到如下一篇文章：

ORA-4030: Out Of Process Memory (QERHJ hash-joi,QERHJ list array) Raised When Using _PGA_MAX_SIZE (Doc ID 1471103.1)

In this Document

---

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
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-joi,QERHJ list array)
   
   
   
2. Review of the instance parameters set reveals:
   
   "_pga_max_size"=614400 KB

Cause

The cause of this problem has been identified in:
- 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.

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.

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.

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:

根據文章的建議，調整了_pga_max_size隱藏引數後，執行相同的SQL語句不再報ORA-04030的錯誤。

    另外，採用共享模式連線資料庫也是避免出現ORA-04030的一種很好的方法。PGA是由堆疊資訊和UGA組成；UGA包含了會話資訊、遊標資訊、SQL工作區等內容，佔據了PGA的大部分空間，採用共享模式連線到資料庫，程式的UGA資訊是放在SGA中的Shared Pool中，如果例項在啟動的時候將SGA完全的固定到記憶體中（利用大頁技術，Linux平臺的HugePage，AIX平臺的Large Page，設定資料庫例項初始化引數pre_page_sga=TRUE,lock_sga=TRUE），例項在啟動後不會有swap in/out，SGA佔用的實際記憶體也不會發生變化，那麼就不會有ORA-04030的錯誤報出。

--end--