Re: ora-4030 pga memory allocation running wild

I'm using auto pga allocation on 9.2.0.3 without any problem.

You don't mention which version.

You can turn it off with 'alter system set workarea_size_policy=manual;

Jared

On Tue, 2003-12-23 at 07:24, Jeroen van Sluisdam wrote:
> 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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).