Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 9 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.