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)

FROM:http://www.askmaclean.com/archives/tune-very-large-hash-join.html