今日,某服务器pga调整为30G,_pga_max_size调整为8G之后(原来是2G,但是one passes语句较多,性能太低),执行出现ORA-12805: parallel query server died unexpectedly错误,即使仅使用了parallel(2),一发一收算4,而且pga_aggregate_target是个软限制,理论上不应该会出现才对,调整为6G之后依然如此。有些提及ulimit设置较低的问题,经查全部为unlimited,故不应该是这个问题。
也有提及,使用manual PGA管理,但是改代码复杂,故没有采纳进行测试。
因为没有使用交换区,内存空闲剩下大约8G(总共物理内存128G)不到,是有可能真的申请不到物理内存的。
MOS上关于这个错误还是挺多的,如下:
We are facing Ora- 4030 errors on 11GR2 Stadard Edition. Please help!!
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)
ORA-06512: at "TENANT2WH_AV.P24_AGG_MAINT", line 2774
ORA-06512: at "TENANT2WH_AV.S_FILL_AGG_AVAIL_15MIN", line 22
ORA-06512: at line 1
I have enabled the trace "alter system set events '4030 trace name heapdump level 536870917;name errorstack level 3'"
Dump continued from file: /u000/app/diag/rdbms/avwhamer/AVWHAMER/trace/AVWHAMER_ora_20381872.trc
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)
========= Dump for incident 261244 (ORA 4030) ========
----- Beginning of Customized Incident Dump(s) -----
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
*** 2011-09-28 00:39:06.429
50% 56 MB, 230 chunks: "kllcqas:kllsltba " SQL
QERGH hash-agg ds=110e73248 dsprt=110d820b8
41% 46 MB,
大概率问题也是在hash group by复杂逻辑的bug上。而且几个语句问题必现,所以要么物理内存不足,要么出发了bug。
查看/var/log/messages,如下:
基本确定是物理内存不足所致。