Hello All,

Platform : HP-UX 11i (64-bit), Oracle 9.2.0.5

A query is failing due to ORA-04030 (out of process memory for callheap). The query involves a FTS on a 18G table. The plan shows a "Hash Join (Outer)" with this large table and a very small one. And then another hash join with these results and records fetched from a table in a remote database.

I watched the PGA_USED_MEM, PGA_ALLOC_MEM and PGA_MAX_MEM values for the SPID of the session that is running the query. What I noticed was when the PGA_USED_MEM becomes 1073741824 (i.e. 1GB), the process fails with ORA-04030 error.

There is more than enough RAM available on the server. More than enough swap space.

I could see it from HP's glancePlus tool also that the "RSS Siz" value became just a little over a GB for this process.

What limit am I hitting ?

On OS side, maxdsiz and mazdsiz_64bit are set to 4G. OS limits of the user running the query ('oracle' in this case) for data is also 4G.

On database side, pga_aggregate_target is 600M, workarea_size_policy is AUTO.

There is no database parameter that is set to 1GB.

What limit am I hitting ?

This is getting very interesting...