good/bad statistics

From time to time I am called in to investigate performance problems as last week.
In this case there was one bad performing query.

Instead of using indexes the optimizer chose full table scans.

When looking at the statistics I thought that the optimizer should have used available indexes here.

In this case my colleagues had gathered new statistics just a week ago.
Looking at dba_tables the last analyzed date was of that date. But looking at dba_tab_col_statistics there were column statistics available from about a year ago.

I then decided to remove all the statistics with dbms_stats procedure and with the analyze command and
gathered new statistics only with dbms_stats. The delete statistics with the analyze command may not be necessary but at some seminar (don't remember when or where) someone said that the analyze command put statistics at other places then the dbms_stats does.

Btw this was 9.2.0.4

I have used this 'trick' on more occasions where the optimizer showed strange behaviour and often this helps.
Has anyone experienced the same behaviour? Can someone confirm what was being said about analyze and dbms_stats

Of course dbms_stats should be used but some applications still use the analyze command.

regards Hans-Peter Received on Sat Oct 11 2008 - 13:52:21 CDT