oracle出现大量的active

Errors in file /opt/oracle/diag/rdbms/ai/ai/trace/ai_pmon_1878.trc:
ORA-04030: out of process memory when trying to allocate 832 bytes (callheap,temporary memory)

oracle出现大量的空进程，alter system kill session 无法结束一个会话。

alter session set sort_area_size = 104857600; 无效
orakill 2774 460 无此命令
select 'ALTER SYSTEM KILL SESSION '||SID||','||SERIAL#||';' from v$session where machine='server1';

select machine,count(*) from v$session group by machine order by count(*);
ulimit -d 32768无效
sysctl -w vm.max_map_count=200000
more /proc/sys/vm/max_map_count
200000
The problem can typically occur if:

a procedure has 1 or more large VARRAYs defined, and is called many times,

a procedure has 1 or more large local variables, and is called many times,

or either of the above situations occur, but the procedure is (accidentally) called in an infinite loop.

The cause of the ORA-4030 error is usually a PL/SQL coding issue, as very larger amounts of data should really be stored in a table, or in the case of an infinite loop, the procedure call needs to have a limit.

The reason why neither PGA_AGGREGATE_TARGET nor _PGA_MAX_SIZE has any impact, is because PL/SQL local variables are not managed by the regular Oracle memory heaps. PL/SQL local variables are just like other programming languages' local variables and should adhere to good coding practices. Their size is only limited by the operating system memory limits.

解决方案建议

根据前文的说明，对这一问题的根本解决办法是对PL/SQL代码进行优化。

在剩余物理内存充足的情况下，也可以通过下面的配置，以突破缺省的4GB的限制：

修改操作系统参数vm.max_map_count，由缺省的65536增大为比如20000。

$ more /proc/sys/vm/max_map_count

$ sysctl -w vm.max_map_count=200000 (for example)

修改数据库参数：

_use_realfree_heap=TRUE

_realfree_heap_pagesize_hint = 262144

缺省的realfree allocator pagesize是64KB，所以缺省最大的4GB。修改为256KB（262144）后，最大限制为16GB。

说明：建议以上修改在测试系统进行详细的测试。

相关日志