RE: Actually PGA allocated > PGA_AGGREGATE_TARGET ?
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 12 Aug 2009 12:26:50 +0200
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:
http://blog.tanelpoder.com/2009/06/24/oracle-memory-troubleshooting-part-3-automatic-top-subheap-dumping-with-heapdump/
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/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
