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');
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?