ORA-04030 (sort subheap,sort key) 错误

kingofworl

SELECT /*+ parallel (a,3) */ ………… from tab …… 查询有时会报ORA-12801: error signaled in parallel query server P206
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)

错误，大神帮看看原因。
以下是一些系统信息，没有出问题当时的v$process记录
数据库版本：10.2.0.4.2   系统hp11.31 物理内存 108G
$ ulimit -a
time(seconds)        unlimited
file(blocks)         unlimited
data(kbytes)         1048576
stack(kbytes)        131072
memory(kbytes)       unlimited
coredump(blocks)     4194303


_pga_large_extent_size
1048576
_use_ism_for_pga
TRUE
pga_aggregate_target
13698596864
_pga_max_size
2147483648

NAME	VALUE	UNIT
aggregate PGA target parameter	13698596864.00	bytes
aggregate PGA auto target	10285065216.00	bytes
global memory bound	1073741824.00	bytes
total PGA inuse	2436659200.00	bytes
total PGA allocated	3840036864.00	bytes
maximum PGA allocated	17235935232.00	bytes
total freeable PGA memory	828768256.00	bytes
process count	2967.00
max processes count	3465.00
PGA memory freed back to OS	48998585008128.00	bytes
total PGA used for auto workareas	168194048.00	bytes
maximum PGA used for auto workareas	6536990720.00	bytes
total PGA used for manual workareas	0.00	bytes
maximum PGA used for manual workareas	2940928.00	bytes
over allocation count	0.00
bytes processed	128192844612608.00	bytes
extra bytes read/written	3188870712320.00	bytes
cache hit percentage	97.57	percent
recompute count (total)	2523203.00

Maclean Liu(刘相兵

1.

这可能是由于并行进程的PGA 分配不合理引起的

2.
10.2的parallel process对继承  PGA参数似乎有问题


workaround:

1. 避免使用 parallel并行方式

2. 调整执行计划 使用索引index full scan、range scan避免实际的排序

3. 使用手动PGA管理绕过该问题:


alter session set workarea_size_policy=MANUAL;
alter session set workarea_size_policy=MANUAL;


alter session set sort_area_size=734003200;
alter session set sort_area_size=734003200;


alter session set "_sort_multiblock_read_count"=128;
alter session set "_sort_multiblock_read_count"=128;