Re: PGA_AGGREGATE_TARGET question

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 15 Aug 2003 14:03:02 GMT
Message-ID: <qK5%a.35388$bo1.16629@news-server.bigpond.net.au>

"Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message news:3F3B9C49.C992515D_at_remove_spam.peasland.com...
> There are some nice views that help you in sizing PGA_AGGREGATE_TARGET
> (P_A_T). If you size this parameter too high, then you will be wasting
> memory, and if it is sufficiently high enough, this can cause swapping
> to occur in your database server.

Hi Brian,

You mentioned above and elsewhere in your post that you will be wasting memory if you set PAT too high. Interested to know how you come to that conclusion as Oracle doesn't allocate the memory unless it's required. You can most certain set it too low and have sub-optimal workarea executions, you can most certainly see it way too low and have the sum of the untunable components of the PGA exceed the PAT regardless and you can set it too high such that you don't have enough memory to service such a target.

But how can you "waste" memory by setting it too high ?

PAT is my favourite 9i new feature so I'm a bit defensive about it :) At the site I currently work at, we set a nice "chunky" PAT, a value for which we have spare memory capacity, a value for which we've reduced disk sorts to never (and improved hash joins) and importantly, a value which we have never actually reached.

For those interested, PAT has saved us approximately 2G in PGA session memory, yet improved performance in the process.

Cheers

Richard Received on Fri Aug 15 2003 - 09:03:02 CDT