Cardinality is so far off from reality

Question

I've got a table where the cardinality for plans just seems waaaaaay too high - despite updating stats and even trying dynamic sampling for Oracle 11gR2.

select count(*) 
from table1 
where col1 = 123 
and col2 = '1';

The estimated cardinality for one value of col1 is roughly 29k, when the actual rows it returns is 637. For another value, its plan cardinality 460k when the actual rows is 67k. In general, this is causing it to choose very bad plans..

I've tried updating stats:

exec DBMS_STATS.gather_table_stats (ownname => 'ME', tabname =>'table1');

or

exec DBMS_STATS.gather_table_stats (ownname => 'ME', tabname =>'table1',estimate_percent =>100);

And I've tried various values of dynamic_samping (1-10), but nothing changes it signifcantly:

select /*+ dynamic_sampling(t1 10) */ count(*) 
from table1 t1 
where col1 = 123 
and col2 = '1';

dyanmic_sampling will alter, but its still off by nearly a factor of 50-100 times.

How can I get better estimates?

Answer 1

The first thing I'd do is check the stats for the columns individually with these queries:

• select count(*) from table1 where col1 = 123;
• select count(*) from table1 where col2 = '1';

If the estimated cardinalities here agree much better with the actual row counts, it means there is some correlation between the columns that the CBO cannot guess based on the histograms generated by a vanilla gather_table_stats. Dynamic sampling should improve things at the cost of increased parse time, but is not a silver bullet.

Alternatively, with 11g, "Oracle Database can also gather statistics on a group of columns within a table"

1. create a column group
2. gather stats on a column group