Re: Improving Performance of Hash Joins

From: Mladen Gogala <>
Date: Tue, 30 Dec 2008 13:58:07 +0000 (UTC)
Message-ID: <gjd9df$ivs$>

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
Received on Tue Dec 30 2008 - 07:58:07 CST

Original text of this message