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

清風艾艾發表於2016-12-19
    近期巡檢中,一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。
相關分析處理過程如下:
    作業系統:RedHat RHEL 6.6
    資料庫版本:ORACLE RAC 11.2.0.4
    
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
注意:上述修改操作需要啟停資料庫叢集及作業系統

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29357786/viewspace-2130952/,如需轉載,請註明出處,否則將追究法律責任。

相關文章