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

ORA4030告警，告警資訊如下：

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：

    針對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

注意：上述修改操作需要啟停資料庫叢集及作業系統