Resolving ORA-04030

[svk]

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...

[mike9]

The problem may be related to bug 3130972.
http://metalink.oracle.com/metalink/...T&id=3130972.8

What do you get when you execute the following query?

select ksppinm, ksppstvl, ksppdesc
from x$ksppi x, x$ksppcv y
where x.indx = y.indx
and (KSPPINM like '%smm%max_size%' or KSPPINM like '%pga%')

[svk]

O/P of the query you provided :

KSPPINM KSPPSTVL KSPPDESC
---------------------------------------------------------------- -------------------- --------------------------------------------------
pga_aggregate_target 314572800 Target size for the aggregate PGA memory consumed
by the instanc

_pga_max_size 209715200 Maximum size of the PGA memory for one process
_smm_max_size 15360 maximum work area size in auto mode (serial)
_smm_px_max_size 92160 maximum work area size in auto mode (global)


So, I guess I was wrong in mentioning pga_aggregate_target value in original post. But, that was a value we had set and did not make a difference.

Thanks.

[Axr2]

Did you check your ulimit for the OS user? Are they set to unlimited?

[svk]

O/P of the query you provided :

KSPPINM KSPPSTVL KSPPDESC
---------------------------------------------------------------- -------------------- --------------------------------------------------
pga_aggregate_target 314572800 Target size for the aggregate PGA memory consumed
by the instanc

_pga_max_size 209715200 Maximum size of the PGA memory for one process
_smm_max_size 15360 maximum work area size in auto mode (serial)
_smm_px_max_size 92160 maximum work area size in auto mode (global)


So, I guess I was wrong in mentioning pga_aggregate_target value in original post. But, that was a value we had set and did not make a difference.

Thanks.

[svk]

OS limits are :

time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 4292870144
stack(kbytes) 392192
memory(kbytes) unlimited
coredump(blocks) 4194303

I guess that BUG may be the culprit. I'll wait to see what Oracle has to say...

[tamilselvan]

===
data(kbytes) 4292870144
stack(kbytes) 392192
==

Set those 2 values to unlimited.
If it does not work, then increase pga_aggregate_target and _pga_max_size to 2GB.

Tamil

[mike9]

your pga / work area size parmeter seems to be standart so they shouln't be the reason for that problem.

But because you said that you got this error when you get close to the 1gb i'm alost sure that the reason for it is the bug 3130972. So I recommand you either to upgrade to 9.2.0.6 or to apply the one off patch.

Increasing _pga_max_size will not solve the problem but, from my experience, rather increase the probability that you hit that bug. However once you applied the patch and if you often do large sorts/group by or large hash joins you may got a much better performance if you increase _pga_max_size to 1 or 2 GB.

[svk]

Thank you all.

We have applied the one-off patch and the job that executes the query is currently running. I'll know the outcome in @ 90 minutes. I'll keep you posted.

[tamilselvan]

Try change the query to use Nested Loop.
See what happens.

Tamil