Hi, I have an ora-4030 problem
related to pga memory
allocation, at least I have concluded sofar This program is batch written
in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with
top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on
oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant
here for migration and he advised to put pga_aggegrate_target
on 250M. Box has 4Gb, shared_pool_size
is 250Mb, SGA is almost 800Mb I issued a tar and Oracle
advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart
that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name
errorstack level 3'; I found the so called
SQL-statement that might be causing this but explaining this plan gave me
an even better plan than on the
oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly
running wild on memory. Based on the docs on metalink
I lowered the pga_aggegrate_target to 160M now and I'm testing this right
now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen |
- RE: ora-4030 pga memory allocation running wild Jeroen van Sluisdam
- RE: ora-4030 pga memory allocation running wild Khedr, Waleed
- Re: ora-4030 pga memory allocation running wild Jared Still
- RE: ora-4030 pga memory allocation running wild Jeroen van Sluisdam