Re: Oracle PGA sizing and ORA-4030
Date: Wed, 25 Nov 2009 04:59:58 -0800 (PST)
Message-ID: <43f84fd1-01fe-43a3-900e-030f107cef7b_at_l2g2000yqd.googlegroups.com>
On Nov 25, 2:56 am, Joerg Rueschescmidt <jrueschenschm..._at_t-online.de> wrote:
> Hello,
>
> we use Oracle 10.2.0.4 on Windows 32 Bit platform and run sometimes into
> ORA-4030 problems.
>
> Current memory settings are :
>
> sga_max_size : 1504M
> sga_target : 1304M
> pga_aggregate_target : 400 M
>
> According this configuration I should not hit the 2 Gbyte limit on 32
> bit platforms.
>
> However, if I look to statistics "maximum PGA allocated" it seems PGA
> can grow above 400M. I think this causes my random ORA-4030 problems.
>
> My questions is
>
> How is it possible memory allocated to PGA can grow above
> pga_aggregate_target value?
>
> Regards ...
>
> Joerg
Carlos is correct that the pga_aggregate_target is a target maximum amount of memory for all sessions. The value may be exceeded quite easily by a single session that is performing bulk collect operations, for instance. Oracle will try to limit the memory to the value specified by the pga_aggregate_target parameter, but if a session needs additional memory and the memory required cannot be freed from other sessions using the PGA memory, Oracle will exceed the pga_aggregate_target specification.
I believe that the _pga_max_size parameter mentioned by Carlos limits the PGA memory used by a single session (it is not clear if this is what Carlos intended to describe). I am not sure if hitting that limit will trigger the ORA-4030, but I am fairly certain that hitting the 2GB per process limit on 32 bit Windows will cause that error. You might want to take a look at the following link: http://dioncho.wordpress.com/2009/07/27/playing-with-ora-4030-error/
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Nov 25 2009 - 06:59:58 CST