Optimizing Database Productivity
fl.peaches
I am confused about whether to set PGA_AGGREGATE_TARGET or
SORT_AREA_SIZE. Whichever you recommend, what is the proper value for
the continuous running of the multiple applications for a database that
is sized between 6GB and 10GB? If you recommend using the
SORT_AREA_SIZE what is the proper value for the corresponding
SORT_AREA_RETAINED_SIZE?
Currently my system is set to values as follows:
sort_area_size : 346640 bytes
Thanks for any help you can give.
HansF
> 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
--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***
Mark D Powell
should still set a value for sort_area_size as there are some
situations where this value will be still be used. I saw this
demonstrated in a post by Jonathan Lewis recently where he was pointing
out the error in a posted article at a "self-proclaimed" expert site.
I do not know where the article is posted but having a reasonable value
set for sort_area_size when you change pga_aggregate_target to a
non-zero value isn't going to hurt anything so I do not see a downside.
HTH -- Mark D Powell --
Matthias Hoys
"Mark D Powell" <Mark....@eds.com> wrote in message
news:1126019821.4...@g44g2000cwa.googlegroups.com...
I believe it is used when having shared server connections.
Matthias
Anurag Varma
Mark D Powell
why I was thinking it appears somewhere else other than his web site.
news.dial.pipex.net
> 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
>
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.
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.
Cheers
Niall
Mladen Gogala
> Actually even if pga_aggregate_target is in use I am pretty sure you
> should still set a value for sort_area_size as there are some
> situations where this value will be still be used. I saw this
> demonstrated in a post by Jonathan Lewis recently where he was pointing
> out the error in a posted article at a "self-proclaimed" expert site.
Wow, not that again! Emotions were flying pretty high on Tom's blog
in that thread, hopefully no flame war will be ignited here.
HansF
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>