Oracle PGA sizing and ORA-4030

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

On Nov 25, 8:56*am, Joerg Rueschescmidt <jrueschenschm...@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

'Target' is a target, not a limit. It seems you are hitting the
(hidden) parameter 'pga_max_size'.

HTH.

Cheers.

Carlos.

On Nov 25, 2:56*am, Joerg Rueschescmidt <jrueschenschm...@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...ra-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.

On Wed, 25 Nov 2009 08:56:03 +0100, Joerg Rueschescmidt 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

Limit for the PGA is the address space limit for the virtual address
space. It's defined by the OS, not by the Oracle instance.



--
http://mgogala.byethost5.com

Joerg Rueschescmidt schrieb:
> 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

Hello all,

okay, if PGA can grow above pga_aggregate_target we can indeed hit the 2
GByte OS limit which causes ORA-4030 finally.

Thanks ...

Joerg

On 26 Nov, 08:54, Joerg Rueschenschmidt <jrueschenschm...@t-online.de>
wrote:
> Hello all,
>
> okay, if PGA can grow above pga_aggregate_target we can indeed hit the 2
> GByte OS limit which causes ORA-4030 finally.
>
> Thanks ...
>
> Joerg- Hide

That's the O/S limitation, unfortunately. I know. I'm stuck using
Windows too.

-g