转 由于PGA单个进程只能使用4GB大小限制导致Oracle 11g版本AUTO SQL TUNING遇到ORA-04030错误
感谢dbdream
由于PGA单个进程只能使用4GB大小限制导致Oracle 11g版本AUTO SQL TUNING遇到ORA-04030错误
Jun122019
今天，用友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
今天，用友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导致的这个错误，就不能这样解决了，只能从上面的修改操作系统单个进程打开内存映射条目数或者修改数据库对应的每个映射条目内存分配大小来解决了。
版权声明：本文为feiyun8616原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。