Re: Actually PGA allocated > PGA_AGGREGATE_TARGET ?

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Tue, 11 Aug 2009 12:14:13 +0900
Message-ID: <43c2e3d60908102014n1c05d61diccbc7383b9bd5af4_at_mail.gmail.com>



As already pointed out, PAT is not a hard limit but just a TARGET.

The acutal maximum PGA size has no limit. You can eat up 10G of PGA even with 100M of PAT. The real question is "why you're using 1.3G of PGA in your app?".

Because you're on Win32, the actual OS limit would be around 1.5G(at default). You would hit ORA-4030 error with that size of PGA allocated. But it seems that you're not allocating that much memory until now.

Moving on to Win64 would eliminate the 1.5G limit, but that does not mean that you have no problem. If your application allocates too much memory compared with your physical RAM, you would see the heavy performance degradation by paging.



Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
http://dioncho.blogspot.com (japanese)
http://ask.ex-em.com (q&a)
================================


2009/8/11 Jiang, Lu <Lu.Jiang_at_umassmed.edu>

> Thanks for the help.
>
>
>
> The problem is that there is not enough memory to adequately size SGA and
> PGA due to Windows 32bit 3 GB limitation. I will migrate it to 64bit
> machine.
>
>
>
>
>
> *From:* Ric Van Dyke [mailto:ric.van.dyke_at_hotsos.com]
> *Sent:* Monday, August 10, 2009 2:10 PM
> *To:* Jiang, Lu; Oracle-L Freelists
> *Subject:* RE: Actually PGA allocated > PGA_AGGREGATE_TARGET ?
>
>
>
> Key words that help in this is that it’s a TARGET not a LIMIT. And as it
> says it “attempts” to stay below this. It can and will (as in your case)
> accede this.
>
>
>
> It looks like at one time oracle needed 1.3G and more recently it needed
> 1.2G but right now is using .9G. From this as a SWAG I’d say you should
> set PGA_AGGREGATE_TARGET to about 1.2G. The issue is that once you go above
> the target, I think it’s SMON that will get excited about trying to bring
> the allocated back done. This would cause a little be more activity at the
> system level. However this is unlikely to be a big deal.
>
>
>
> -----------------------
>
> Ric Van Dyke
>
> Hotsos Enterprises
>
> -----------------------
>
>
>
> *Hotsos Symposium *
>
> *March 7 – 11, 2010 *
>
> *Be there.*
>
>
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jiang, Lu
> *Sent:* Monday, August 10, 2009 1:56 PM
> *To:* Oracle-L Freelists
> *Subject:* Actually PGA allocated > PGA_AGGREGATE_TARGET ?
>
>
>
> Hi all,
>
>
>
> The following is my query result against v$pgastat view.
> PGA_AGGREGATE_TARGET is set to 1G. According to Oracle' documentation, Oracle
> should attempts to keep the amount of private memory below the
> PGA_AGGREGATE_TARGET specified. But from the following it looks Oracle
> allocated 1.2G PGA memory, and the maximum PGA allocated is 1.3GB which is
> far above 1G (PGA_AGGREGATE_TARGET), I am confused. Could anyone share
> some light on this?
>
>
>
> aggregate PGA target parameter
>
> 1,073,741,824.00
>
> bytes
>
> aggregate PGA auto target
>
> 85,053,440.00
>
> bytes
>
> global memory bound
>
> 107,366,400.00
>
> bytes
>
> total PGA inuse
>
> 984,952,832.00
>
> bytes
>
> total PGA allocated
>
> 1,259,997,184.00
>
> bytes
>
> maximum PGA allocated
>
> 1,347,652,608.00
>
> bytes
>
>
>
> Thanks,
>
> Lu
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Aug 10 2009 - 22:14:13 CDT

Original text of this message