RE: hash joins and pga/temp space?
- From: Adrian <ade.turner@xxxxxxxxx>
- To: <peters@xxxxxxxxx>, "'_oracle_L_list'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Nov 2006 20:12:18 -0000
Re your recollection:
"The memory allocated to a single SQL operator is limited to min(5%
PGA_AGGREGATE_TARGET, 100MB) for serial operations and for parallel
operations to min(30% PGA_AGGREGATE_TARGET/DOP, 5% PGA_AGGREGATE_TARGET,
100MB) (DOP=Degree of Parallelism). As you see you will never get more than
100MB for one sql operation. This 100 MB limit is in fact set by
_pga_max_size/2 where _pga_max_size = 200MB by default."
In 10gR2 at least, _PGA_MAX_SIZE is by default set to something like 2GB.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Peter Sylvester
Sent: 14 November 2006 17:27
To: _oracle_L_list
Subject: Re: hash joins and pga/temp space?
I don't have all the answers, but I did run into some issues with hash
joins degenerating into nested-loop joins when the record count
increased. I ended up cranking up the PGA_AGGREGATE_TARGET since I am
doing the auto-PGA thing (10.2.0.2/Solaris10), and it seemed to solve my
problem. I also added USE_HASH hints, but I *think* it will still do
something else if it does not have enough memory for the hash table.
Unfortunately I do not have a representative test system for this
warehouse style implementation.
Note that if you use the USE_HASH hint the smaller table should be the
first parameter.
I also recall reading that there are some upper limits for the hash_area
and sort_area memory sizes (200mb?) when using the auto-PGA, and some
undocumented parameters may be required for getting larger sizes. In my
case I was able to get away with adjusting PGA_AGGREGATE_TARGET up so
did not get into that.
There is some interested reading on hash joins (as well as sorting
costs, and lots of other things...) in Jonathan Lewis' book "Cost-Based
Oracle Fundamentals", which you may want to check out.
--Peter
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
