Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: hash joins and pga/temp space?

RE: hash joins and pga/temp space?

From: Adrian <ade.turner_at_gmail.com>
Date: Tue, 14 Nov 2006 20:12:18 -0000
Message-ID: <455a2322.12a81a2f.75bd.1619@mx.google.com>


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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 14 2006 - 14:12:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US