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 scheme _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, ryan_gaffuri@xxxxxxxxxxx <ryan_gaffuri@xxxxxxxxxxx> 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 <peters@xxxxxxxxx> > 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 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 > > -- > //www.freelists.org/webpage/oracle-l > >
-- ---------------------------------------- http://www.oracleboliviaspecialists.com/ Oracle Certified Profesional 9i 10g Orace Certified Professional Developer 6i 10 years of experience from Oracle 7 to Oracle10g and developer 6i