Effect of wrong cardinality estimate on Hash join run time execution (Fanout)

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 : 12.1.0.2.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) :

good cardinality

Workarea stats :

1 pass stats

10104 trace extract :

good estimate

Observation :

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 :

bad cardinality

WorkArea stats :

multi pass stats

10104 Trace extract :

Bad selectivity

Observation :

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 😀

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s