Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: hash joins and pga/temp space?

From: Juan Carlos Reyes Pacheco <>
Date: Tue, 14 Nov 2006 18:16:05 -0400
Message-ID: <>

I don't think but does make sense to change the hash_area_size  parameter when you set pga to automatic?, maybe like sga, when you set a value it set the minimum value.

Or to test some of this parameter, I found a pair could make sense.

_enqueue_hash enqueue hash table length
_enqueue_hash_chain_latches enqueue hash chain latches
_lm_res_hash_bucket number of resource hash buckets
_db_block_hash_buckets Number of database block hash buckets
_db_block_hash_latches Number of database block hash latches
_enable_hash_overflow TRUE - enable hash cluster overflow based on SIZE
_kgl_hash_collision whether KGL hash collision is possible
_hash_join_enabled enable/disable hash join
hash_area_size size of in-memory hash work area
_hash_multiblock_io_count number of blocks hash join will read/write at once
_spr_use_hash_table use hash table for spreadsheet
_right_outer_hash_enable Right Outer/Semi/Anti Hash Enabled
_cursor_plan_hash_version version of cursor plan hash value
_gby_hash_aggregation_enabled enable group-by and aggregation using hash
_sql_hash_debug Hash value of the SQL statement to debug
_olap_object_hash_class OLAP Object Hash Table Class
_olap_dimension_corehash_max OLAP Dimension In-Core Hash Table Maximum Size
_olap_dimension_corehash_class OLAP Dimension In-Core Hash Table Class
_kffmap_hash_size size of kffmap_hash table
_kffmop_hash_size size of kffmop_hash table

On 11/14/06, <> wrote:
> I am really just looking for a way to predictively forecast how much hash
> space I need for a given query. Assuming the plan is the same. So as I add
> data I can know in advance what I will need instead of a try and find out.
> The system is in development. I don't have production data. So we are
> testing with smaller data sets.
> -------------- Original message --------------
> From: Peter Sylvester <>
> > 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 (, 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 s ort_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
> >
> > --
> >
> >
> >


Oracle Certified Profesional 9i 10g
Orace Certified Professional Developer 6i

10 years of experience from Oracle 7 to Oracle10g and developer 6i

Received on Tue Nov 14 2006 - 16:16:05 CST

Original text of this message