ORACLE ORA-04030之 out of process memory when trying to allocate

    近期巡检中，一oracle 11g rac节点出现ORACLE ORA-04030之 out of process memory when trying to allocate报错， 查询ORACLE官方MOS确定是：BUG11852492，原因是用户会话单进程占用PGA超过4GB异常终止，根据MOS文章1325100.1的建议：可以根据服务器实际情况及SQL运行的实际情况放开PGA单进程使用内存空间的限制到16GB。

相关分析处理过程如下：

ORA4030告警，告警信息如下：

**************************** 查看数据库告警日志 ************************** 
ERROR IN ALERT LOG FILE - LAST 3 DAYS 
===================================== 
15/12/2016 22:44:13 Thu Dec 15 22:44:13 2016 
Thu Dec 15 22:44:13 2016 
ORA-04030: out of process memory when trying to allocate 64 bytes (kxs-heap-c,allocator state) 
ORA-04030: out of process memory when trying to allocate 624 bytes (kxs-heap-c,MSQ2) 
ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer) 
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_498/orcl2_j002_142221_i498.trc: 
See Note 411.1 at My Oracle Support for error and packaging details. 
Use ADRCI or Support Workbench to package the incident. 
trc文件核心报错信息如下：

Dump file /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_498/orcl2_j002_142221_i498.trc 
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
ORACLE_HOME = /u01/app/oracle/product/11.2.0 
System name: Linux 
Node name: rac02 
Release: 4.1.12-37.4.1.el6uek.x86_64 
Version: #2 SMP Tue May 17 07:23:38 PDT 2016 
Machine: x86_64 
Instance name: orcl2 
Redo thread mounted by this instance: 2 
Oracle process number: 62 
Unix process pid: 142221, image: oracle@rac02 (J002) 
*** 2016-12-15 22:44:02.053 
*** SESSION ID:(2853.28977) 2016-12-15 22:44:02.053 
*** CLIENT ID:() 2016-12-15 22:44:02.053 
*** SERVICE NAME:(SYS$USERS) 2016-12-15 22:44:02.053 
*** MODULE NAME:(DBMS_SCHEDULER) 2016-12-15 22:44:02.053 
*** ACTION NAME:(ORA$AT_SQ_SQL_SW_447) 2016-12-15 22:44:02.053 
Dump continued from file: /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_j002_142221.trc 
ORA-04030: out of process memory when trying to allocate 624 bytes (kxs-heap-c,MSQ2) 
ORA-04030: out of process memory when trying to allocate 64 bytes (kxs-heap-c,allocator state) 

TOP 10 MEMORY USES FOR THIS PROCESS 
--------------------------------------- 
*** 2016-12-15 22:44:05.989 
73% 2995 MB, 49066306 chunks: "allocator state " SQL 
kxs-heap-c ds=0x7efff8e40478 dsprt=0xc0d1f20 
23% 948 MB, 64420 chunks: "free memory " 
top call heap ds=0xc0d1f20 dsprt=(nil) 
1% 40 MB, 377503 chunks: "strdef: qcopCreateStr " 
TCHK^dccb1c9d ds=0x7efff884b258 dsprt=0x7efff8cbc948 
1% 36 MB, 17796 chunks: "qkkele " SQL 
kxs-heap-c ds=0x7efff8e40478 dsprt=0xc0d1f20 
0% 12 MB, 308978 chunks: "chedef : qcuatc " 
TCHK^dccb1c9d ds=0x7efff884b258 dsprt=0x7efff8cbc948 
0% 10 MB, 17193 chunks: "MSQ2 " SQL 
kxs-heap-c ds=0x7efff8e40478 dsprt=0xc0d1f20 
0% 8557 KB, 3615 chunks: "miscellaneous " SQL 
ds=(nil) dsprt=(nil) 
0% 7737 KB, 165191 chunks: "free memory " SQL 
kxs-heap-c ds=0x7efff8e40478 dsprt=0xc0d1f20 
0% 2854 KB, 16606 chunks: "kkpap: kkpapDAAll " 
TCHK^dccb1c9d ds=0x7efff884b258 dsprt=0x7efff8cbc948 
0% 2613 KB, 8732 chunks: "permanent memory " SQL 
kxs-heap-c ds=0x7efff8e40478 dsprt=0xc0d1f20 

Private memory usage per Oracle process 
------------------------- 
Top 10 processes: 
------------------------- 
(percentage is of 4653 MB total allocated memory) 
88% pid 62: 3153 MB used of 4103 MB allocated <= CURRENT PROC 
1% pid 14: 40 MB used of 46 MB allocated 
1% pid 41: 41 MB used of 45 MB allocated (1088 KB freeable) 
1% pid 42: 41 MB used of 45 MB allocated (1088 KB freeable) 
1% pid 44: 41 MB used of 45 MB allocated (1088 KB freeable) 
1% pid 13: 40 MB used of 44 MB allocated (768 KB freeable) 
1% pid 15: 39 MB used of 40 MB allocated (192 KB freeable) 
0% pid 43: 18 MB used of 19 MB allocated 
0% pid 20: 13 MB used of 17 MB allocated (1408 KB freeable) 
0% pid 22: 13 MB used of 16 MB allocated (1088 KB freeable)

pid 62: 3153 MB used of 4103 MB allocated 
------------------------------------ 
Begin session detail for pid 62 
sid: 2853 ser: 28977 audsid: 52112 user: 90/****
flags: (0x8010041) USR/- flags_idl: (0x1) BSY/-/-/-/-/- 
flags2: (0x40009) -/-/INC 
pid: 62 O/S info: user: oracle, term: UNKNOWN, ospid: 142221 
image: oracle@rac02 (J002) 
client details: 
O/S info: user: oracle, term: UNKNOWN, ospid: 142221 
machine: rac02 program: oracle@rac02 (J002) 
application name: DBMS_SCHEDULER, hash value=2478762354 
action name: ORA$AT_SQ_SQL_SW_447, hash value=1421285536 
current SQL: 
/* SQL Analyze(2853,1) */ select *

from (select row_.*, rownum NumRow 
from (with tmp_detail as (select /*+ parallel(d,4) */ 
distinct d.pid, d.item_id, d.item_name 
from dw_billdetail d 
where 1 = 1 
and d.table_par >= '20160401' 
and d.table_par <= '20160430' 
and d.dept_id = '消化内科' 
or instr(d.deptname, '消化内科') > 0) 
SELECT /*+ordered use_nl(a d) use_nl_with_index(a IX_BILL_TAB_PAR) FIRST_ROWS(100)*/ 
......
(nvl(bof.CT, 0) + nvl(cc.CT, 0) + nvl(dof.CT, 0))) as FILE_COUNT 
FROM DW_Bill a 
left join dw_bill_ex e 
on e.table_par = a.table_par 
and a.hisid = e.billid 
left join DW_ZD_ClaimType d 
on d.CLASS_ID = a.CLAIM_TYPE 
left join DW_ZD_Patient patient 
on patient.id = a.PATIENT_ID 
left join DW_ZD_BenefitGroup rylb 
on rylb.CLASS_ID = a.BENEFIT_GROUP_ID 
left join DW_ZD_BenefitPlan cblx 
on cblx.class_id = a.benefit_type 
left join dw_zd_region zdg 
on a.bmi_code = zdg.region_id 
left join (SELECT distinct (m.HISID), n.CT 
FROM DW_BILLFILES m 
LEFT JOIN (SELECT HISID, 
COUNT(distinct filename) AS CT 
FROM DW_BILLFILES 
GROUP BY HISID) n 
ON m.HISID = n.HISID) cc 
on a.HISID = cc.HISID 
left join (select count(distinct filename) as CT, 
BILL_NO, 
HOSPITAL_ID 
from dw_opinionfiles 
group by BILL_NO, HOSPITAL_ID) dof 
on a.bill_no = dof.bill_no 
and a.hospital_id = dof.hospital_id 
left join (select count(distinct filename) as CT, 
BILL_NO, 
HOSPITAL_ID 
from bill_opinionfiles 
group by BILL_NO, HOSPITAL_ID) bof 
on a.bill_no = bof.bill_no 
and a.hospital_id = bof.hospital_id 
join (select distinct pid from tmp_detail) aa 
on a.hisid = aa.pid 
where 1 = 1 
and a.TABLE_PAR >= '20160401' 
and a.TABLE_PAR <= '20160430' 
and (bitand(a.rule_bit, 4) = 4 or 
......
1125899906842624 or 
bitand(a.rule_bit, 288230376151711744) = 
288230376151711744 or a.rule_bit = 0) 
and a.HOSPITAL_ID = '1101' 
and a.claim_type = '2' 
and exists 
(select distinct bb.DETAIL_ID 
from SYS_USER_GP aa 
inner join Sys_Gp_Detail bb 
on aa.GP_ID = bb.GP_ID 
where aa.USER_ID = 55 
and bb.detail_id = a.hospital_id) 
order by table_par, HISID) row_ 
where rownum <= 3000 
) 
where NumRow > 0；
    由以上信息可以看出90号用户的62号进程执行的作业中有sql语句占用过多的PGA空间（使用了4103MB的3153MB，达到88%），根据错误信息：ORA-04030:(kxs-heap-c,MSQ2) (kxs-heap-c,allocator state) 进一步 查询ORACLE官方MOS确定是：BUG11852492，原因是用户会话单进程占用PGA超过4GB异常终止。根据MOS文章1325100.1的建议：可以根据服务器实际情况及SQL运行
的实际情况放开PGA单进程使用内存空间的限制到16GB。
    针对PGA单进程4GB内存空间的限制， 查证当前操作系统相关限制参数设置为4GB：

 [root@rac02 ~]#  more /proc/sys/vm/max_map_count
65530
[root@rac02 ~]# ssh rac01
root@rac01's password: 
Last login: Fri Sep 23 15:19:29 2016 from  192.168.10.229
[root@rac01 ~]#   more /proc/sys/vm/max_map_count
65530
[root@rac01 ~]#  

    针对PGA单进程4GB内存空间的限制，使用如下sql语句查证oracle数据库相关隐含参数设置为4GB：_realfree_heap_pagesize_hint=65536

 select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
  from x$ksppi a, x$ksppcv b
 where a.indx = b.indx and a.ksppinm like '_realfree_heap_pagesize_hint';
    如果实际情况可以，oracle官方允许PGA单进程设置为16GB，相关操作如下：

SOLUTION 
Change the upper limit at either the OS or at the database level: 
Change the page count at the OS level: 
more /proc/sys/vm/max_map_count 
sysctl -w vm.max_map_count=262144 (for example) 
**Please note the above changes will revert upon host reboot unless the changes are permanently made in the /etc/sysctl.conf file ** 
Adjust the realfree heap pagesize within the database by setting the following parameters in the init/spfile and restart the database. 
For versions 11.2.0.4 and lower: 
_use_realfree_heap=TRUE 
_realfree_heap_pagesize_hint = 262144 
For 12.1 and higher: 
_use_realfree_heap=TRUE 
_realfree_heap_pagesize = 262144

注意：上述修改操作需要启停数据库集群及操作系统