Re: PGA_AGGREGATE_TARGET question

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sat, 16 Aug 2003 00:21:44 GMT
Message-ID: <sOe%a.35716$bo1.4970@news-server.bigpond.net.au>

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3f3cf654$0$15041$ed9e5944_at_reading.news.pipex.net...
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:qK5%a.35388$bo1.16629_at_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.

>

> Hi Richard

>

> I *believe* that whether or not Oracle pre-allocates PAT is platform
> dependent, I'm pretty sure that on Windows at least you get the memory up
> front.

>
Hi Niall

No, that's not correct (try setting it to say 20G on a 512M box and see how much memory is allocated... ;).

The whole point of PAT is that it's a target which ideally is never reached meaning that workarea activity is performed optimally. Any memory above that being used has no purpose in being allocated. If the target is reached, then probably, some non-optimal activity has occurred. If it's exceeded, then your PAT was not even close.

The only thing preventing you from setting to a massive target as suggested by Brian is your available memory. You may not have enough of the stuff to prevent some non-optimal activity and you don't want too high a PAT to cause memory problems.

You may be confusing things with SGA_MAX_SIZE which does allocate the lot on some platforms ?

Cheers

Richard Received on Fri Aug 15 2003 - 19:21:44 CDT