一、 问题背景
收到开发反馈,系统报表运行过程中报错,一看发现是ORA-4030,内存的问题
查看alert日志,发现期间有大量ORA-4030报错,并且主要是pga相关的
打开trace文件,可以看到报错进程使用内存接近4G
但是查看pga参数设置,发现设置的上限是20G,完全没到,并且期间总的PGA使用率也不高
二、 报错原因
MOS搜索发现 Doc ID 1325100.1 文档,看到现象也是
You are running a PL/SQL package or procedure and are consistently encountering an ORA-4030 when the process uses 4GB. The values for the _PGA_MAX_SIZE and PGA_AGGREGATE_TARGET has been set to values even greater than the 4GB, but yet the same errors persist when 4GB is used.
而报错原因是:操作系统有默认单个进程最多只能打开65530个内存映射条目的限制。
cat /proc/sys/vm/max_map_count
65530
数据库有与之对应的隐含参数_realfree_heap_pagesize_hint,默认是65536,意思是realfree当前的分配大小是65536 bytes(64K)。
SELECT x.ksppinm NAME, y.ksppstvl VALUE,x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.indx = y.indx AND x.ksppinm = '_realfree_heap_pagesize_hint';
这个隐含参数表示操作系统上每个内存映射条目大小是64K,而操作系统上又限制每个进程最多能打开65530个内存映射条目,因此,每个进程使用PGA就不能超过4G。
select 65536*65530/1024/1024/1024 GB from dual;
GB
----------
3.99963379
三、 解决方法
从问题的原因就可以猜到起码有两种解决思路:加大单个进程可打开的内存映射条目大小,加大每个内存映射条目大小,注意不要改得太大。前者改操作系统参数,后者改数据库隐含参数。
1. 加大单个进程可打开的内存映射条目大小
vi /etc/sysctl.conf
#修改为262144,单进程可用PGA变为16G
vm.max_map_count=262144
#参数生效
sysctl –p
这样每个内存映射条目大小64K,262144个条目就是16G,应该足够用了。这也是官方文档推荐的改法。
2. 加大每个内存映射条目大小
alter system set "_realfree_heap_pagesize_hint"=262144 scope=spfile;
上面的修改也会对应单进程PGA最大可用内存为16G,但是要重启数据库生效,并且隐含参数通常不建议修改
3. 优化SQL
对于小数据库,优先考虑这种方法,分析为什么要占用那么多PGA,进行优化。
四、 注意事项
根据文档 Doc ID 14119856.8,11.2.0.3及以下版本还有一个bug,会导致即使设置了上面的参数,单进程最大可用PGA也只能到16G,文档给出的 workaround是 Disable real free memory allocator。但是普通业务真的不建议单进程内存上限设这么大。
alter system set "_use_realfree_heap"=FALSE scope=spfile;
参考
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 (Doc ID 1325100.1)
Bug 14119856 - ORA-4030 occurs at 16gb of PGA even if it could grow much larger (Doc ID 14119856.8)