Re: Optimizing Database Productivity

From: HansF <News.Hans_at_telus.net>
Date: Sat, 10 Sep 2005 22:41:34 GMT
Message-ID: <pan.2005.09.10.22.45.59.719019@telus.net>

On Sat, 10 Sep 2005 20:30:27 +0100, news.dial.pipex.net interested us by writing:

> HansF wrote:

>> On Tue, 06 Sep 2005 05:42:41 -0700, fl.peaches interested us by writing:
>>
>>

>>>Hello!
>>> I am confused about whether to set PGA_AGGREGATE_TARGET or
>>> SORT_AREA_SIZE. Whichever you recommend, what is the proper value for

>>
>>
>> Pretty simple really ...
>>
>> If YOU want to manage it, set SORT_AREA_SIZE (and HASH_AREA_SIZE,
>> BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE) and ensure
>> PGA_AGGREGATE_TARGET=0. The 'Configuring SORT_AREA_SIZE' section of the
>> 'Performance Tuning Guide and Reference' manual was written to help with
>> this.
>>
>> If you want the INSTANCE to manage it automatically, set the
>> PGA_AGGREGATE_TARGET to a non-zero value. The value to start and how to
>> adjust the target are well described in the 'Automatic PGA Memory
>> Management' section of the 'Performance Tuning Guide and Reference' manual
>>

> It isn't quite as simple as this Hans. As well as setting P_A_T to a 
> non-zero figure, you should set it to the amount of memory system wide 
> you wish to reserve for sort and hash operations, you also need to 
> ensure that WORKAREA_SIZE_POLICY is set to AUTO.

Yup. However, AUTO happens to be the default setting for W_S_P. Hopefully OP will read the manuals as the W_S_P discussion is in the same are as the P_A_T discussion.

One thing that surprises me a bit in these discussions is the emphasis on SORT_AREA_SIZE whereas HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE seem to be ignored entirely, not even mentioned in passing.

<Amusement>
In my defense of the 'pretty simple' aspect, from the 10GR1 Concepts, note the second sentance <g>

"You can automatically and globally manage the size of SQL work areas. The database administrator simply needs to specify the total size dedicated to PGA memory for the Oracle instance by setting the initialization parameter PGA_AGGREGATE_TARGET. </Amusement>

> 
> The discussion later in the thread about needing to set S_A_S etc for 
> shared server connections only applies up to IIRC 10g 10g and above AIUI 
> use P_A_T if set for both dedicated and shared connections.

Since P_A_T was introduced in 9iR1, could we rephrase that as 'only applies to 9i' <g>

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** I no longer assist with top-posted newsgroup queries ***

Received on Sat Sep 10 2005 - 17:41:34 CDT