pga setting Oracle 9.2 recommodations?

From: Manuela Mueller <mueller_m_at_fiz-chemie.de>
Date: Fri, 13 Sep 2002 13:09:18 +0200
Message-ID: <3D81C75E.70179B87@fiz-chemie.de>

OS: Linux Linux 2.4.18-4GB (Suse 8.0)
RDBMS: Oracle 9.2.0.1.0, Standard Edition Physical RAM: 4 GB
Swap: 1 GB

Memory Summary for Instance:

SGA regions                       Size in Bytes
------------------------------ ----------------
Database Buffers                  1,392,508,928
Fixed Size                              452,304
Redo Buffers                            667,648
Variable Size                       201,326,592
                               ----------------
sum                               1,594,955,472

Dear All,
we are in the process of tuning our application, some sort of data warehouse system. Once a month large amounts of data (around 5 million rows, with LOB's) are loaded into the DB. To improve performance during the load, the PK on the table to be loaded is disabled, column is null. After completion of the load a PL/SQL procedure performs a bulk collect combined with forall statement and updates all rows in the PK column with a sequence value.
I run the PL/SQL program on my test box with 500000 rows without any problems (1 GB RAM, 1 GB swap space).
After the initial tests I tried the same thing on the development box with 1,200000 rows. The program aborted with

 ORA-04030: out of process memory when trying to allocate 210444 bytes (callheap,DARWIN)

I gathered statistics with statspack.snap during the execution of the program. The report showed that I run out of PGA. <snip of report>
init.ora parameters:

Parameter Name                Begin value                       
----------------------------- ---------------------------------


...

db_block_size                 8192
db_cache_size                 1392508928


...

java_pool_size                33554432


...

large_pool_size               33554432
open_cursors                  300
pga_aggregate_target          75497472


...

sga_max_size                  1594955472
shared_pool_size              100663296
sort_area_size                2097152

PGA related stuff from report:

PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written

--------------- ---------------- -------------------------
          100.0                2                         0

Warning: pga_aggregate_target was set too low for current workload, as this

          value was exceeded during this interval. Use the PGA Advisory view

          to help identify a different value for pga_aggregate_target.

                                             %PGA  %Auto   %Man

  PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
  Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------

---

B 72 56 20.9 0.0 .0 .0 .0 3,686
E 72 56 21.5 0.0 .0 .0 .0 3,686

---

</end snip report>

We are using Auto memory management (workarea_size_policy AUTO). The memory advice in the statistics report suggests at least 432 MB for PGA. I know I can set this parameter dynamically with 'Alter system..'. This is the first time I need such a large PGA value, so I lack experience on possible side effects.
Are there any other things to consider? Any warning or experiences?

Any suggestions are welcome, TIA and have a nice weekend Manuela Mueller Received on Fri Sep 13 2002 - 06:09:18 CDT