RE: Actually PGA allocated > PGA_AGGREGATE_TARGET ?
Date: Tue, 11 Aug 2009 11:44:18 -0400
Message-ID: <BBB2B987DB6E504D89FC45EA314CE9890E69F0C9_at_edmtpmail01.ad.umassmed.edu>
Thanks Dion. The big PGA allocated could be caused by an apps bug.
3GB switch is using on the DB machine so that about 2.7GB can be used for SGA, PGA and user connections. The statistics at the bottom was from two weeks ago. It looks OK now after I applied the apps patch to fix the connection leakage. I found db connection leakage caused by apps quite a while ago but was hesitated to apply the fix since it is One Off patch with some risk. After tested on a test environment for over one month I finally applied it on prod system 10 days ago. The patch fixed db connection leakage but caused another issue, fortunately another patch fixed it. The highest PGA allocated is below 1 GB so far and I will keep monitoring it.
The reason to migrate the DB to 64bit machine is that Oracle recommended pga_aggregate_target value for EBS prod database is recently changed to at least 2GB (it was 1GB). And also I am still seeing 'SGA/PGA was inadequately sized' messages from the DB diagnostic views. Really hope there is another way can work this out.
From: Dion Cho [mailto:ukja.dion_at_gmail.com]
Sent: Monday, August 10, 2009 11:14 PM
To: Jiang, Lu
Cc: Oracle-L Freelists
Subject: Re: Actually PGA allocated > PGA_AGGREGATE_TARGET ?
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-lReceived on Tue Aug 11 2009 - 10:44:18 CDT