Tune Very Large Hash Join

set timing on;
alter session set workarea_size_policy=MANUAL;
alter session set workarea_size_policy=MANUAL;

alter session set db_file_multiblock_read_count=512;
alter session set db_file_multiblock_read_count=512;

alter session set events '10351 trace name context forever, level 128';

alter session set hash_area_size=524288000;
alter session set hash_area_size=524288000;

alter session set "_hash_multiblock_io_count"=128;
alter session set "_hash_multiblock_io_count"=128;

alter session enable parallel query;

select /*+   pq_distribute(a hash,hash) parallel(a) parallel(b) */ column1,column2....
  from source_tab a, driving_tab b
 where  condition
 ;

--PQ_DISTRIBUTE(tab,out,in) How to distribute rows from tab in a PQ
--(out/in may be HASH/NONE/BROADCAST/PARTITION)

© 2009 – 2011, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. How ORACLE CBO Calculate Join Cardinality with Join Elimination Filter?
2. Optimizer Selects the Merge Join Cartesian Despite the Hints
3. Hash Joins Implementation and Tuning
4. SCRIPT – to Tune the ‘SESSION_CACHED_CURSORS’ and ‘OPEN_CURSORS’ Parameters
5. Tuning Large Sorts
6. Handling and resolving unshared cursors/large version_counts