RE: Actually PGA allocated > PGA_AGGREGATE_TARGET ?
- From: "Jiang, Lu" <Lu.Jiang@xxxxxxxxxxxx>
- To: "Dion Cho" <ukja.dion@xxxxxxxxx>
- Date: Tue, 11 Aug 2009 11:44:18 -0400
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@xxxxxxxxx]
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@xxxxxxxxxxxx>
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@xxxxxxxxxx]
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
Other related posts:
