RE: Actually PGA allocated > PGA_AGGREGATE_TARGET ?
Date: Wed, 12 Aug 2009 12:26:50 +0200
Message-Id: <756340951_at_web.de>
You say that you've already solved your problem by installing application patches, but I just wanted to point out that below figures give you an important clue:
Whenever the "aggregate PGA auto target" is very small in comparison to the "PGA aggregate target" defined, like in your case (85MB vs. 1GB), then this tells you that PGA memory has been consumed by allocation types that are not under control of Oracle, i.e. some processes have either used "manual workareas", which can be verified by looking at the "total PGA used for manual workareas" and "maximum PGA used for manual workareas" statistics of the same view, or "PL/SQL" large collections or "Java" memory allocations have been used.
This leads to the effect that there is not much memory left for Oracle to be used for "automatically sized workareas", which in turn can lead to suboptimal performance of your SQL execution, since the automatic workareas are very small and therefore perform potentially suboptimal using 1-pass or multi-pass executions including spilling to disk, since Oracle tries hard to obey the defined "PGA aggregate target".
So in such cases it is vital to understand where these individual memory allocations come from that prevent Oracle from effectively using the "PGA aggregate target". You can search for the processes with the largest PGA consumption e.g. using V$PROCESS or V$PROCESS_MEMORY and in case you're unsure what exactly goes on use a "heapdump" to find out the details. Tanel Poder has a good description and his "heapdump_analyzer" script:
Note that using large PL/SQL collections for PL/SQL bulk fetching or execution can cause some serious PGA memory leaks / overallocations in some versions of Oracle:
http://jonathanlewis.wordpress.com/2009/06/07/pga-leaks/
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
> 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
Neu: WEB.DE Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate für nur 19,99 Euro/mtl.!* http://produkte.web.de/go/02/
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 12 2009 - 05:26:50 CDT