Re: pga setting Oracle 9.2 recommodations?

In article <o7rg9.294430$_91.360265_at_rwcrnsc51.ops.asp.att.net>, "Michael says...
>
>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.

Search trhough the group for my reply to a similar question on PGA_AGGREGATE_TARGET, and you'll probably understand why it needs to be set so high.

Pete
>
>"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
>
>

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Fri Sep 13 2002 - 16:32:53 CDT