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: [email protected] (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 select * from (select row_.*, rownum NumRow 

from (with tmp_detail as (select  

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  

......

(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：  [[email protected] ~]#  more /proc/sys/vm/max_map_count

65530

[[email protected] ~]# ssh rac01

[email protected]'s password: 

Last login: Fri Sep 23 15:19:29 2016 from  192.168.10.229

[[email protected] ~]#   more /proc/sys/vm/max_map_count

65530

[[email protected] ~]#       針對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 注意：上述修改操作需要啟停資料庫叢集及作業系統