Re: pga setting Oracle 9.2 recommodations?

I'd say go for it.
I assume you are using dedicated server.For "Shared server" you would want to play with
the SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_SIZE MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE parameters.

Also, your sort_area_size looks a little small. I am not much of an expert on this, so it you need a definitive answer before
proceeding, wait for somebody more experienced to reply.

"Manuela Mueller" <mueller_m_at_fiz-chemie.de> wrote in message news:3D81C75E.70179B87_at_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 - 14:36:52 CDT