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)

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569