ORA-04030 with PGA_AGGREGATE_TARGET

[Mahesh Hardikar]

Hello ,

Oracle 9i (9.0.1.0.0) on HP-UX 11.11
Physical RAM : 2GB

Oracle init.ora parameters :

DB_BLOCK_SIZE=4096
DB_CACHE_SZIE=314572800
LOG_BUFFER 10M
JAVA_POOL 30M
LARGE_POOL 1M
SHARED_POOL 250M

With these parameters constant , I set

WORKAREA_SIZE_POLICY=AUTO
PGA_AGGREGATE_TARGET=157286400 (Calculation is (RAM*0.8)*0.2 for OLTP
Systems from Oracle Manuals )

Woth these settings I get memory errors ORA-04030
If I set
WORKAREA_SIZE_POLICY=MANUAL & set SORT_AREA_SIZE=10M , obviously I do
not Memory errors.

My question ,

What should be reasonable value for PGA_AGGREGATE_TARGET to avoid
memory errors ?

Also I would like to know if there is any heavy load if I turn on
Multimaster Replication ?

Thanks & Regards,
Mahesh Hardikar

[Daniel Roy]

Hi Mahesh,
I don't have the luxury of running my Oracle on HP-UX, but I setup
my Win 2000 laptop with pga_aggregate_target to RAM*80%*20%, and it
worked OK. By the way, I run Oracle 9.2. But on Metalink, I found note
43507.1, which might possibly apply to your case, depending on your
patch level. Look in particular for sections where it discusses
"kernel panic" caused by the setting of pga_aggregate_target, and that
should point you in the right direction of which patches might help.

HTH

Daniel

Hello ,

Oracle 9i (9.0.1.0.0) on HP-UX 11.11
Physical RAM : 2GB

Oracle init.ora parameters :

DB_BLOCK_SIZE=4096
DB_CACHE_SZIE=314572800
LOG_BUFFER 10M
JAVA_POOL 30M
LARGE_POOL 1M
SHARED_POOL 250M

With these parameters constant , I set

WORKAREA_SIZE_POLICY=AUTO
PGA_AGGREGATE_TARGET=157286400 (Calculation is (RAM*0.8)*0.2 for OLTP
Systems from Oracle Manuals )

Woth these settings I get memory errors ORA-04030
If I set
WORKAREA_SIZE_POLICY=MANUAL & set SORT_AREA_SIZE=10M , obviously I do
not Memory errors.

My question ,

What should be reasonable value for PGA_AGGREGATE_TARGET to avoid
memory errors ?

Also I would like to know if there is any heavy load if I turn on
Multimaster Replication ?

Thanks & Regards,
Mahesh Hardikar

[frits hoogland]

can you find a tracefile with the actual error in it?
this sounds like a ulimit or kernel param (max data/source/heap) size
problem.

frits

Mahesh Hardikar wrote:

Hello ,

Oracle 9i (9.0.1.0.0) on HP-UX 11.11
Physical RAM : 2GB

Oracle init.ora parameters :

DB_BLOCK_SIZE=4096
DB_CACHE_SZIE=314572800
LOG_BUFFER 10M
JAVA_POOL 30M
LARGE_POOL 1M
SHARED_POOL 250M

With these parameters constant , I set

WORKAREA_SIZE_POLICY=AUTO
PGA_AGGREGATE_TARGET=157286400 (Calculation is (RAM*0.8)*0.2 for OLTP
Systems from Oracle Manuals )

Woth these settings I get memory errors ORA-04030
If I set
WORKAREA_SIZE_POLICY=MANUAL & set SORT_AREA_SIZE=10M , obviously I do
not Memory errors.

My question ,

What should be reasonable value for PGA_AGGREGATE_TARGET to avoid
memory errors ?

Also I would like to know if there is any heavy load if I turn on
Multimaster Replication ?

Thanks & Regards,
Mahesh Hardikar