Re: PGA_AGGREGATE_TARGET question

Richard Foote wrote:
>
> "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

Thats true, but besides the n% for a serial task and m% for parallel, aren't there some algorithms which allocate space et al based on the total pga figure. I can't remember the specifics, but there was an OracleWorld '02 presentation which talked about all sorts of variables, many of which were based on the pga_agg setting.

I'm not sure if setting it to 20G on a 512M box would be the best thing :-)

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"