转 由于PGA单个进程只能使用4GB大小限制导致Oracle 11g版本AUTO SQL TUNING遇到ORA-04030错误

感谢dbdream

http://www.dbdream.com.cn/2019/06/%E7%94%B1%E4%BA%8Epga%E5%8D%95%E4%B8%AA%E8%BF%9B%E7%A8%8B%E5%8F%AA%E8%83%BD%E4%BD%BF%E7%94%A84gb%E5%A4%A7%E5%B0%8F%E9%99%90%E5%88%B6%E5%AF%BC%E8%87%B4oracle-11g%E7%89%88%E6%9C%ACauto-sql-tuning/

由于PGA单个进程只能使用4GB大小限制导致Oracle 11g版本AUTO SQL TUNING遇到ORA-04030错误

Jun122019

作者：dbdream   发布：2019-06-12 11:02   分类：Oracle, oracle 11g     抢沙发  

 

今天，用友NC的一个数据库又出问题了，这次遇到的是ORA-04030错误：

01	Mon Jun 10 22:05:19 2019

02	Dumping diagnostic data in directory=[cdmp_20190610220519], requested by (instance=1, osid=101922 (J001)), summary=[incident=48330].

03	Errors in file /u01/app/oracle/diag/rdbms/ncdb1/ncdb1/incident/incdir_48329/ncdb1_j001_101922_i48329.trc:

04	ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)

05	ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)

06	Dumping diagnostic data in directory=[cdmp_20190610220521], requested by (instance=1, osid=101922 (J001)), summary=[incident=48331].

07	Mon Jun 10 22:06:19 2019

08	Sweep [inc2][48331]: completed

09	Sweep [inc2][48329]: completed

10	Mon Jun 10 22:08:56 2019

11	Errors in file /u01/app/oracle/diag/rdbms/ncdb1/ncdb1/trace/ncdb1_j001_101922.trc  (incident=48332):

12	ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)

13	Incident details in: /u01/app/oracle/diag/rdbms/ncdb1/ncdb1/incident/incdir_48332/ncdb1_j001_101922_i48332.trc

14	Use ADRCI or Support Workbench to package the incident.

15	See Note 411.1 at My Oracle Support for error and packaging details.

下面是相关的trace文件的头部的部分信息：

1	*** 2019-06-10 22:12:35.316

2	*** SESSION ID:(650.32147) 2019-06-10 22:12:35.316

3	*** CLIENT ID:() 2019-06-10 22:12:35.316

4	*** SERVICE NAME:(SYS$USERS) 2019-06-10 22:12:35.316

5	*** MODULE NAME:(DBMS_SCHEDULER) 2019-06-10 22:12:35.316

6	*** ACTION NAME:(ORA$AT_SQ_SQL_SW_1803) 2019-06-10 22:12:35.316

通过ACTION NAME部分可用看出来，这是AUTO SQL TUNING触发的错误。下面是触发这个错误的SQL。

001	/* SQL Analyze(650,1) */

002	select SUM(nvl(nnum, 0)) nnum,

003	SUM(nvl(nmny, 0)) nmny,

004

datatype,

005	pk_brsetting

006	from ((((select sum(nvl(nnum, 0)) nnum,

007	sum(nvl(nmny, 0)) nmny,

008	'begin' datatype,

009	'1001D21000000021WO54' pk_brsetting

010	from (((select sum(nvl(nabnum, 0)) nnum,

011	sum(nvl(nabmny, 0)) nmny

012	from ia_periodnab

013	where pk_group = '0001A1100000000003Z3'

014	and pk_book = '1001A110000000001GQU'

015	and pk_org = '1001A110000000001GTH'

016	and dr = 0

017	and caccountperiod = '2018-12') union

018	all((select sum(nvl(ninnum, 0)) nnum,

019	sum(nvl(ninmny, 0)) nmny

020	from ia_monthin

021	where pk_group = '0001A1100000000003Z3'

022	and pk_book = '1001A110000000001GQU'

023	and pk_org = '1001A110000000001GTH'

024	and dr = 0

025	and caccountperiod >= '2019-01'

026	and caccountperiod <= '2019-04') union all

027	(select sum(-nvl(noutnum, 0)) nnum,

028	sum(-nvl(noutmny, 0)) nmny

029	from ia_monthout

030	where pk_group = '0001A1100000000003Z3'

031	and pk_book = '1001A110000000001GQU'

032	and pk_org = '1001A110000000001GTH'

033	and dr = 0

034	and caccountperiod >= '2019-01'

035	and caccountperiod <= '2019-04')))) a) union all

036	(select sum(nvl(nnum, 0)) nnum,

037	sum(nvl(nmny, 0)) nmny,

038	'end' datatype,

039	'1001D21000000021WO54' pk_brsetting

040	from (((select sum(nvl(nabnum, 0)) nnum, sum(nvl(nabmny, 0)) nmny

041	from ia_periodnab

042	where pk_group = '0001A1100000000003Z3'

043	and pk_book = '1001A110000000001GQU'

044	and pk_org = '1001A110000000001GTH'

045	and dr = 0

046	and caccountperiod = '2018-12') union

047	all((select sum(nvl(ninnum, 0)) nnum,

048	sum(nvl(ninmny, 0)) nmny

049	from ia_monthin

050	where pk_group = '0001A1100000000003Z3'

051	and pk_book = '1001A110000000001GQU'

052	and pk_org = '1001A110000000001GTH'

053	and dr = 0

054	and caccountperiod >= '2019-01'

055	and caccountperiod <= '2019-05') union all

056	(select sum(-nvl(noutnum, 0)) nnum,

057	sum(-nvl(noutmny, 0)) nmny

058	from ia_monthout

059	where pk_group = '0001A1100000000003Z3'

060	and pk_book = '1001A110000000001GQU'

061	and pk_org = '1001A110000000001GTH'

062	and dr = 0

063	and caccountperiod >= '2019-01'

064	and caccountperiod <= '2019-05')))) b)) union

065	all((select sum(nvl(nnum, 0)) nnum,

066	sum(nvl(nmny, 0)) nmny,

067	'begin' datatype,

068	'1001D21000000021WZP4' pk_brsetting

069	from (((select sum(nvl(nnum, 0)) nnum, sum(nvl(nmny, 0)) nmny

070	from ia_goodsledger

071	where pk_group = '0001A1100000000003Z3'

072	and pk_book = '1001A110000000001GQU'

073	and pk_org = '1001A110000000001GTH'

074	and dr = 0

075	and fintransitflag = 0

076	and caccountperiod < '2019-05') union all

077	(select sum(-nvl(nnum, 0)) nnum, sum(-nvl(nmny, 0)) nmny

078	from ia_goodsledger

079	where pk_group = '0001A1100000000003Z3'

080	and pk_book = '1001A110000000001GQU'

081	and pk_org = '1001A110000000001GTH'

082	and dr = 0

083	and fintransitflag = 1

084	and caccountperiod < '2019-05'))) c) union all

085	(select sum(nvl(nnum, 0)) nnum,

086	sum(nvl(nmny, 0)) nmny,

087	'end' datatype,

088	'1001D21000000021WZP4' pk_brsetting

089	from (((select sum(nvl(nnum, 0)) nnum, sum(nvl(nmny, 0)) nmny

090	from ia_goodsledger

091	where pk_group = '0001A1100000000003Z3'

092	and pk_book = '1001A110000000001GQU'

093	and pk_org = '1001A110000000001GTH'

094	and dr = 0

095	and fintransitflag = 0

096	and caccountperiod <= '2019-05') union all

097	(select sum(-nvl(nnum, 0)) nnum, sum(-nvl(nmny, 0)) nmny

098	from ia_goodsledger

099	where pk_group = '0001A1100000000003Z3'

100	and pk_book = '1001A110000000001GQU'

101	and pk_org = '1001A110000000001GTH'

102	and dr = 0

103	and fintransitflag = 1

104	and caccountperiod <= '2019-05'))) d))) union

105	all((((select sum(nvl(nnum, 0)) nnum,

106	sum(nvl(nmny, 0)) nmny,

107	'credit' datatype,

108	'1001D21000000021WO54' pk_brsetting

109	from (select sum(nvl(noutnum, 0)) nnum,

110	sum(nvl(noutmny, 0)) nmny

111	from ia_monthout

112	where pk_group = '0001A1100000000003Z3'

113	and pk_book = '1001A110000000001GQU'

114	and pk_org = '1001A110000000001GTH'

115	and dr = 0

116	and caccountperiod >= '2019-05'

117	and caccountperiod <= '2019-05') g) union all

118	(select sum(nvl(nnum, 0)) nnum,

119	sum(nvl(nmny, 0)) nmny,

120	'debit' datatype,

121	'1001D21000000021WO54' pk_brsetting

122	from (select sum(nvl(ninnum, 0)) nnum,

123	sum(nvl(ninmny, 0)) nmny

124	from ia_monthin

125	where pk_group = '0001A1100000000003Z3'

126	and pk_book = '1001A110000000001GQU'

127	and pk_org = '1001A110000000001GTH'

128	and dr = 0

129	and caccountperiod >= '2019-05'

130	and caccountperiod <= '2019-05') h)))) union all

131	(select sum(nnum) nnum, sum(nmny) nmny, datatype, pk_brsetting

132	from (((select nvl(nnum, 0) nnum,

133	nvl(nmny, 0) nmny,

134

case

135	when fintransitflag = 0 then

136

'debit'

137

else

138

'credit'

139	end datatype,

140	'1001D21000000021WZP4' pk_brsetting

141	from ia_goodsledger

142	where pk_group = '0001A1100000000003Z3'

143	and pk_book = '1001A110000000001GQU'

144	and pk_org = '1001A110000000001GTH'

145	and dr = 0

146	and caccountperiod >= '2019-05'

147	and caccountperiod <= '2019-05'))) o

148	group by datatype, pk_brsetting)) s

149	group by datatype, pk_brsetting

从这里也可用看出这个错误是由AUTO SQL TUNING触发的，这个错误的原因是遇到了单个进程最多只能使用PGA只有4G的限制导致，下面是trace中的相关信息。

01	Dumping Work Area Table (level=1)

02	=====================================

03

04	Global SGA Info

05	---------------

06

07	global target:    12856 MB

08	auto target:       8467 MB

09	max pga:           2048 MB

10	pga limit:         4096 MB

11	pga limit known:  0

12	pga limit errors:     0

13

14	pga inuse:         3447 MB

15	pga alloc:         4705 MB

16	pga freeable:       164 MB

17	pga freed:        13398681 MB

18	pga to free:          0 %

19	broker request:       0

20

21	pga auto:             0 MB

22	pga manual:           0 MB

23

24	pga alloc  (max):  5882 MB

25	pga auto   (max):  2084 MB

26	pga manual (max):     1 MB

27

28	# workareas     :     1

29	# workareas(max):   106

可用看到，当前的PGA设置是12G，但是单个进程最多只能使用4G，根据MOS文档PLSQL Procedure Causing ORA-04030: (pga heap,control file i/o buffer) And ORA-04030: (koh-kghu sessi,pmuccst: adt/record) or ORA-04030: (koh-kghucall ,pmucalm coll) Errors (文档 ID 1325100.1)的说明，这是因为操作系统默认单个进程最多只能打开65530个内存映射条目限制的。

1	[root@SL010A-NCDB1 ~]# cat /proc/sys/vm/max_map_count

2

65530

数据库也有和这个相对应的隐含参数_realfree_heap_pagesize_hint，默认是65536。

01	SQL> col NAME for a30

02	SQL> col VALUE for a20

03	SQL> col DESCRIB for a45

04	SQL> set lines 200

05	SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE,x.ksppdesc describ

06	2  FROM SYS.x$ksppi x, SYS.x$ksppcv y

07	3  WHERE x.indx = y.indx AND x.ksppinm LIKE '%realfree%';

08

09	NAME                           VALUE                DESCRIB

10	------------------------------ -------------------- ---------------------------------------------

11	_realfree_heap_max_size        32768                minimum max total heap size, in Kbytes

12	_realfree_heap_pagesize_hint   65536                hint for real-free page size in bytes

13	_realfree_heap_mode            0                    mode flags for real-free heap

14	_use_realfree_heap             TRUE                 use real-free based allocator for PGA memory

_realfree_heap_pagesize_hin隐含参数的意思是realfree当前的分配大小是65536 bytes，也就是64K，也就对应操作系统上每个内存映射条目的内存大小是64K，而操作系统上又限制每个进程最多能打开65530个内存映射条目，因此，每个进程使用PGA就不能超过4G。

1	SQL> select 65536*65530/1024/1024/1024 GB  from dual;

2

3

GB

4	----------

5	3.99963379

那么对应的就有两种解决方案，一种是调整操作系统单个进程打开内存映射条目的大小，另一种就是在数据库调整对应的分配单元大小。

操作系统调整单个进程打开内存映射条目大小，需要修改sysctl.conf文件，在最下面增加下面这一行即可。

1	[root@SL010A-NCDB1 ~]# vi /etc/sysctl.conf

2	--在最下面增加下面这行

3	vm.max_map_count=262144

然后通过sysctl –p命令使之生效。这样每个映射条目大小64K，262144个条目就是16G，应该足够用了。

或者在数据库调整realfree的分配单元的大小，但是这个隐含参数是静态参数，需要重启数据库才能生效。

1	SQL> alter system set "_realfree_heap_pagesize_hint"=262144 scope=spfile;

2

3	System altered.

然后重启数据库，使之生效。

以上两种方法，不管是修改操作系统的限制还是修改数据库的参数，只修改一个就可用，如果两种方法都修改，需要设置合理的值，避免单个进程使用的内存限制过大，万一有个进程出了问题，可能直接就把内存耗尽了。

对本案例来讲，还有一种解决方法，当然只对本案例有效，因为本案例ORA-04030错误是由于AUTO SQL TUNING导致的，而AUTO SQL TUNING对我来讲又没啥用，完全可用通过禁用AUTO SQL TUNING来解决这个问题。可用通过下马的方法关闭AUTO SQL TUNING。

1

BEGIN

2	dbms_auto_task_admin.disable(

3	client_name => 'sql tuning advisor',

4	operation => NULL,

5	window_name => NULL);

6

END;

7

/

如果需要开启AUTO SQL TUNING，可用通过下面的方法来开启。

1

BEGIN

2	dbms_auto_task_admin.enable(

3	client_name => 'sql tuning advisor',

4	operation => NULL,

5	window_name => NULL);

6

END;

7

/

针对单个进程只能使用4G的PGA导致的ORA-04030错误的问题，具体还要看是什么原因导致的，本案例特殊，由于是AUTO SQL TUNING触发的，而AUTO SQL TUNING对我这套数据库来讲并没什么用，所以可用通过禁用AUTO SQL TUNING的方式来解决，如果是业务程序的存储过程等PL/SQL导致的这个错误，就不能这样解决了，只能从上面的修改操作系统单个进程打开内存映射条目数或者修改数据库对应的每个映射条目内存分配大小来解决了。

由于PGA单个进程只能使用4GB大小限制导致Oracle 11g版本AUTO SQL TUNING遇到ORA-04030错误

Jun122019

作者：dbdream   发布：2019-06-12 11:02   分类：Oracle, oracle 11g     抢沙发  

 

今天，用友NC的一个数据库又出问题了，这次遇到的是ORA-04030错误：

01	Mon Jun 10 22:05:19 2019

02	Dumping diagnostic data in directory=[cdmp_20190610220519], requested by (instance=1, osid=101922 (J001)), summary=[incident=48330].

03	Errors in file /u01/app/oracle/diag/rdbms/ncdb1/ncdb1/incident/incdir_48329/ncdb1_j001_101922_i48329.trc:

04	ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)

05	ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)

06	Dumping diagnostic data in directory=[cdmp_20190610220521], requested by (instance=1, osid=101922 (J001)), summary=[incident=48331].

07	Mon Jun 10 22:06:19 2019

08	Sweep [inc2][48331]: completed

09	Sweep [inc2][48329]: completed

10	Mon Jun 10 22:08:56 2019

11	Errors in file /u01/app/oracle/diag/rdbms/ncdb1/ncdb1/trace/ncdb1_j001_101922.trc  (incident=48332):

12	ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)

13	Incident details in: /u01/app/oracle/diag/rdbms/ncdb1/ncdb1/incident/incdir_48332/ncdb1_j001_101922_i48332.trc

14	Use ADRCI or Support Workbench to package the incident.

15	See Note 411.1 at My Oracle Support for error and packaging details.

下面是相关的trace文件的头部的部分信息：

1	*** 2019-06-10 22:12:35.316

2	*** SESSION ID:(650.32147) 2019-06-10 22:12:35.316

3	*** CLIENT ID:() 2019-06-10 22:12:35.316

4	*** SERVICE NAME:(SYS$USERS) 2019-06-10 22:12:35.316

5	*** MODULE NAME:(DBMS_SCHEDULER) 2019-06-10 22:12:35.316

6	*** ACTION NAME:(ORA$AT_SQ_SQL_SW_1803) 2019-06-10 22:12:35.316

通过ACTION NAME部分可用看出来，这是AUTO SQL TUNING触发的错误。下面是触发这个错误的SQL。

001	/* SQL Analyze(650,1) */

002	select SUM(nvl(nnum, 0)) nnum,

003	SUM(nvl(nmny, 0)) nmny,

004

datatype,

005	pk_brsetting

006	from ((((select sum(nvl(nnum, 0)) nnum,

007	sum(nvl(nmny, 0)) nmny,

008	'begin' datatype,

009	'1001D21000000021WO54' pk_brsetting

010	from (((select sum(nvl(nabnum, 0)) nnum,

011	sum(nvl(nabmny, 0)) nmny

012	from ia_periodnab

013	where pk_group = '0001A1100000000003Z3'

014	and pk_book = '1001A110000000001GQU'

015	and pk_org = '1001A110000000001GTH'

016	and dr = 0

017	and caccountperiod = '2018-12') union

018	all((select sum(nvl(ninnum, 0)) nnum,

019	sum(nvl(ninmny, 0)) nmny

020	from ia_monthin

021	where pk_group = '0001A1100000000003Z3'

022	and pk_book = '1001A110000000001GQU'

023	and pk_org = '1001A110000000001GTH'

024	and dr = 0

025	and caccountperiod >= '2019-01'

026	and caccountperiod <= '2019-04') union all

027	(select sum(-nvl(noutnum, 0)) nnum,

028	sum(-nvl(noutmny, 0)) nmny

029	from ia_monthout

030	where pk_group = '0001A1100000000003Z3'

031	and pk_book = '1001A110000000001GQU'

032	and pk_org = '1001A110000000001GTH'

033	and dr = 0

034	and caccountperiod >= '2019-01'

035	and caccountperiod <= '2019-04')))) a) union all

036	(select sum(nvl(nnum, 0)) nnum,

037	sum(nvl(nmny, 0)) nmny,

038	'end' datatype,

039	'1001D21000000021WO54' pk_brsetting

040	from (((select sum(nvl(nabnum, 0)) nnum, sum(nvl(nabmny, 0)) nmny

041	from ia_periodnab

042	where pk_group = '0001A1100000000003Z3'

043	and pk_book = '1001A110000000001GQU'

044	and pk_org = '1001A110000000001GTH'

045	and dr = 0

046	and caccountperiod = '2018-12') union

047	all((select sum(nvl(ninnum, 0)) nnum,

048	sum(nvl(ninmny, 0)) nmny

049	from ia_monthin

050	where pk_group = '0001A1100000000003Z3'

051	and pk_book = '1001A110000000001GQU'

052	and pk_org = '1001A110000000001GTH'

053	and dr = 0

054	and caccountperiod >= '2019-01'

055	and caccountperiod <= '2019-05') union all

056	(select sum(-nvl(noutnum, 0)) nnum,

057	sum(-nvl(noutmny, 0)) nmny

058	from ia_monthout

059	where pk_group = '0001A1100000000003Z3'

060	and pk_book = '1001A110000000001GQU'

061	and pk_org = '1001A110000000001GTH'

062	and dr = 0

063	and caccountperiod >= '2019-01'

064	and caccountperiod <= '2019-05')))) b)) union

065	all((select sum(nvl(nnum, 0)) nnum,

066	sum(nvl(nmny, 0)) nmny,

067	'begin' datatype,

068	'1001D21000000021WZP4' pk_brsetting

069	from (((select sum(nvl(nnum, 0)) nnum, sum(nvl(nmny, 0)) nmny

070	from ia_goodsledger

071	where pk_group = '0001A1100000000003Z3'

072	and pk_book = '1001A110000000001GQU'

073	and pk_org = '1001A110000000001GTH'

074	and dr = 0

075	and fintransitflag = 0

076	and caccountperiod < '2019-05') union all

077	(select sum(-nvl(nnum, 0)) nnum, sum(-nvl(nmny, 0)) nmny

078	from ia_goodsledger

079	where pk_group = '0001A1100000000003Z3'

080	and pk_book = '1001A110000000001GQU'

081	and pk_org = '1001A110000000001GTH'

082	and dr = 0

083	and fintransitflag = 1

084	and caccountperiod < '2019-05'))) c) union all

085	(select sum(nvl(nnum, 0)) nnum,

086	sum(nvl(nmny, 0)) nmny,

087	'end' datatype,

088	'1001D21000000021WZP4' pk_brsetting

089	from (((select sum(nvl(nnum, 0)) nnum, sum(nvl(nmny, 0)) nmny

090	from ia_goodsledger

091	where pk_group = '0001A1100000000003Z3'

092	and pk_book = '1001A110000000001GQU'

093	and pk_org = '1001A110000000001GTH'

094	and dr = 0

095	and fintransitflag = 0

096	and caccountperiod <= '2019-05') union all

097	(select sum(-nvl(nnum, 0)) nnum, sum(-nvl(nmny, 0)) nmny

098	from ia_goodsledger

099	where pk_group = '0001A1100000000003Z3'

100	and pk_book = '1001A110000000001GQU'

101	and pk_org = '1001A110000000001GTH'

102	and dr = 0

103	and fintransitflag = 1

104	and caccountperiod <= '2019-05'))) d))) union

105	all((((select sum(nvl(nnum, 0)) nnum,

106	sum(nvl(nmny, 0)) nmny,

107	'credit' datatype,

108	'1001D21000000021WO54' pk_brsetting

109	from (select sum(nvl(noutnum, 0)) nnum,

110	sum(nvl(noutmny, 0)) nmny

111	from ia_monthout

112	where pk_group = '0001A1100000000003Z3'

113	and pk_book = '1001A110000000001GQU'

114	and pk_org = '1001A110000000001GTH'

115	and dr = 0

116	and caccountperiod >= '2019-05'

117	and caccountperiod <= '2019-05') g) union all

118	(select sum(nvl(nnum, 0)) nnum,

119	sum(nvl(nmny, 0)) nmny,

120	'debit' datatype,

121	'1001D21000000021WO54' pk_brsetting

122	from (select sum(nvl(ninnum, 0)) nnum,

123	sum(nvl(ninmny, 0)) nmny

124	from ia_monthin

125	where pk_group = '0001A1100000000003Z3'

126	and pk_book = '1001A110000000001GQU'

127	and pk_org = '1001A110000000001GTH'

128	and dr = 0

129	and caccountperiod >= '2019-05'

130	and caccountperiod <= '2019-05') h)))) union all

131	(select sum(nnum) nnum, sum(nmny) nmny, datatype, pk_brsetting

132	from (((select nvl(nnum, 0) nnum,

133	nvl(nmny, 0) nmny,

134

case

135	when fintransitflag = 0 then

136

'debit'

137

else

138

'credit'

139	end datatype,

140	'1001D21000000021WZP4' pk_brsetting

141	from ia_goodsledger

142	where pk_group = '0001A1100000000003Z3'

143	and pk_book = '1001A110000000001GQU'

144	and pk_org = '1001A110000000001GTH'

145	and dr = 0

146	and caccountperiod >= '2019-05'

147	and caccountperiod <= '2019-05'))) o

148	group by datatype, pk_brsetting)) s

149	group by datatype, pk_brsetting

从这里也可用看出这个错误是由AUTO SQL TUNING触发的，这个错误的原因是遇到了单个进程最多只能使用PGA只有4G的限制导致，下面是trace中的相关信息。

01	Dumping Work Area Table (level=1)

02	=====================================

03

04	Global SGA Info

05	---------------

06

07	global target:    12856 MB

08	auto target:       8467 MB

09	max pga:           2048 MB

10	pga limit:         4096 MB

11	pga limit known:  0

12	pga limit errors:     0

13

14	pga inuse:         3447 MB

15	pga alloc:         4705 MB

16	pga freeable:       164 MB

17	pga freed:        13398681 MB

18	pga to free:          0 %

19	broker request:       0

20

21	pga auto:             0 MB

22	pga manual:           0 MB

23

24	pga alloc  (max):  5882 MB

25	pga auto   (max):  2084 MB

26	pga manual (max):     1 MB

27

28	# workareas     :     1

29	# workareas(max):   106

可用看到，当前的PGA设置是12G，但是单个进程最多只能使用4G，根据MOS文档PLSQL Procedure Causing ORA-04030: (pga heap,control file i/o buffer) And ORA-04030: (koh-kghu sessi,pmuccst: adt/record) or ORA-04030: (koh-kghucall ,pmucalm coll) Errors (文档 ID 1325100.1)的说明，这是因为操作系统默认单个进程最多只能打开65530个内存映射条目限制的。

1	[root@SL010A-NCDB1 ~]# cat /proc/sys/vm/max_map_count

2

65530

数据库也有和这个相对应的隐含参数_realfree_heap_pagesize_hint，默认是65536。

01	SQL> col NAME for a30

02	SQL> col VALUE for a20

03	SQL> col DESCRIB for a45

04	SQL> set lines 200

05	SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE,x.ksppdesc describ

06	2  FROM SYS.x$ksppi x, SYS.x$ksppcv y

07	3  WHERE x.indx = y.indx AND x.ksppinm LIKE '%realfree%';

08

09	NAME                           VALUE                DESCRIB

10	------------------------------ -------------------- ---------------------------------------------

11	_realfree_heap_max_size        32768                minimum max total heap size, in Kbytes

12	_realfree_heap_pagesize_hint   65536                hint for real-free page size in bytes

13	_realfree_heap_mode            0                    mode flags for real-free heap

14	_use_realfree_heap             TRUE                 use real-free based allocator for PGA memory

_realfree_heap_pagesize_hin隐含参数的意思是realfree当前的分配大小是65536 bytes，也就是64K，也就对应操作系统上每个内存映射条目的内存大小是64K，而操作系统上又限制每个进程最多能打开65530个内存映射条目，因此，每个进程使用PGA就不能超过4G。

1	SQL> select 65536*65530/1024/1024/1024 GB  from dual;

2

3

GB

4	----------

5	3.99963379

那么对应的就有两种解决方案，一种是调整操作系统单个进程打开内存映射条目的大小，另一种就是在数据库调整对应的分配单元大小。

操作系统调整单个进程打开内存映射条目大小，需要修改sysctl.conf文件，在最下面增加下面这一行即可。

1	[root@SL010A-NCDB1 ~]# vi /etc/sysctl.conf

2	--在最下面增加下面这行

3	vm.max_map_count=262144

然后通过sysctl –p命令使之生效。这样每个映射条目大小64K，262144个条目就是16G，应该足够用了。

或者在数据库调整realfree的分配单元的大小，但是这个隐含参数是静态参数，需要重启数据库才能生效。

1	SQL> alter system set "_realfree_heap_pagesize_hint"=262144 scope=spfile;

2

3	System altered.

然后重启数据库，使之生效。

以上两种方法，不管是修改操作系统的限制还是修改数据库的参数，只修改一个就可用，如果两种方法都修改，需要设置合理的值，避免单个进程使用的内存限制过大，万一有个进程出了问题，可能直接就把内存耗尽了。

对本案例来讲，还有一种解决方法，当然只对本案例有效，因为本案例ORA-04030错误是由于AUTO SQL TUNING导致的，而AUTO SQL TUNING对我来讲又没啥用，完全可用通过禁用AUTO SQL TUNING来解决这个问题。可用通过下马的方法关闭AUTO SQL TUNING。

1

BEGIN

2	dbms_auto_task_admin.disable(

3	client_name => 'sql tuning advisor',

4	operation => NULL,

5	window_name => NULL);

6

END;

7

/

如果需要开启AUTO SQL TUNING，可用通过下面的方法来开启。

1

BEGIN

2	dbms_auto_task_admin.enable(

3	client_name => 'sql tuning advisor',

4	operation => NULL,

5	window_name => NULL);

6

END;

7

/

针对单个进程只能使用4G的PGA导致的ORA-04030错误的问题，具体还要看是什么原因导致的，本案例特殊，由于是AUTO SQL TUNING触发的，而AUTO SQL TUNING对我这套数据库来讲并没什么用，所以可用通过禁用AUTO SQL TUNING的方式来解决，如果是业务程序的存储过程等PL/SQL导致的这个错误，就不能这样解决了，只能从上面的修改操作系统单个进程打开内存映射条目数或者修改数据库对应的每个映射条目内存分配大小来解决了。