oracle中04030报错,ORACLE ORA-04030之 out of process memory when trying to allocate

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

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