Finding the optimum fanout (represent the number of partition the build input will be split into) is one of the keys to optimum performance of a hash join. “High fan-out can produce a large number of small partitions, resulting in inefficient I/O. At the other extreme, small fan-out can produce a small number of large partitions, which will not fit in hash memory.”(as quoted by hash-joins-implementation-and-tuning)
In this blog post i will not discuss how the hash join algorithm work in fact it’s well covered on other location like “Jonathan lewis book oracle cost based optimizer” and http://docslide.us/documents/hash-joins-implementation-and-tuning.html (old but still helpful) and Steve Adams http://www.scribd.com/doc/16883906/Adams-Hash-Joins-Oracle .
The calculation of the optimum fanout is based on the Build input estimated size which is Dependant on the column sizes and row counts.
Here is a quick example to demonstrate how sufficiently inaccurate cardinality can affect hash join runtime performance :
TEST Database : 126.96.36.199.0
drop table test_stat1;
drop table test_stat2;
create table test_stat1 as select lpad(‘*’,2000)||level as a from dual connect by level < 2000000;
create table test_stat2 as select lpad(‘*’,2000)||level as a from dual connect by level < 2000000;
alter session set events ‘10104 trace name context forever’;
select /*+ monitor */ count(*) from (select * from test_stat1 t1,test_stat2 t2 where t1.a = t2.a) h;
select /*+ monitor */ count(*) from (select /*+ cardinality(t1 3000) cardinality(t2 3000) */ * from test_stat1 t1,test_stat2 t2 where t1.a = t2.a) h;
alter session set events ‘10104 trace name context off’;
We will use hash join trace event 10104 to get more detail and the cardinality hint to fake the statistics.
Example with good cardinality estimate :
Sql monitor output showing the new row source metrics in the other columns (Tanel poder in http://fr.slideshare.net/tanelp/sql-monitoring-in-oracle-database-12c ) (Check v$sql_monitor_statname for stats description) :
Workarea stats :
10104 trace extract :
we can see clearly that the build input was split into 128 partitions and the hash join completed on 1 pass in approximatively 9 min.
Example with bad cardinality estimate :
Sql monitor :
WorkArea stats :
10104 Trace extract :
Wow the query elapsed time is now 13min !! The build input was split into 8 partitions and since none of the partition fit in the work area the hash join completed in 4 pass.
So this clearly show that sufficiently inaccurate statistics can not only influence execution plan but also runtime execution.
That’s it 😀