Re: Optimizing Database Productivity

From: news.dial.pipex.net <Niall.litchfield_at_dial.pipex.com>
Date: Sat, 10 Sep 2005 20:30:27 +0100
Message-ID: <432333af$0$29903$cc9e4d1f@news.dial.pipex.com>

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.

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 Received on Sat Sep 10 2005 - 14:30:27 CDT