Re: Improving Performance of Hash Joins
Date: Tue, 30 Dec 2008 13:58:07 +0000 (UTC)
Message-ID: <gjd9df$ivs$3@solani.org>
On Fri, 26 Dec 2008 11:46:53 -0800, zigzagdna wrote:
> Below is information about PGA memory usage. I see my
> PGA_AGGREATE_TARGET is set to 614M, yet only 28M is used, why??: So
> increaseing it won't help imporive performance because HASH_JOIN does
> not seem to use all of the memory.
PGA_AGGREGATE_TARGET is an AGGREGATE, which means it covers the total
use of private global areas, all across the instance. Each session can
use much smaller amount of memory, defined by the _pga_max_size parameter.
Approximately half of the _pga_max_size can be used for sort and hash
operations (as determined by _smm_max_size)
Jože Senegačnik wrote a great paper about 9i/10.1 memory management. To
my knowledge, that paper is mostly accurate for 10.2 and 11g, unless
automatic memory management is used in 11g. 11g makes it possible to
define a single lump of memory that we want to devote to Oracle and allow
oracle to distribute it as it needs.
One more thing: the size of total PGA on the system should be expressed
in GB, rather then MB, on the most of the modern server systems.
CAUTIONARY NOTE:
These parameters are undocumented and unsupported. Please, do not use them
unless directed so by Oracle Support or Sybrand Bakker.
-- Mladen Gogala http://mgogala.freehostia.comReceived on Tue Dec 30 2008 - 07:58:07 CST