Oracle 单进程可用PGA为4G限制导致的ORA-4030报错

一、 问题背景

收到开发反馈，系统报表运行过程中报错，一看发现是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)