Re: good/bad statistics

From: ErikYkema <>
Date: Sat, 11 Oct 2008 12:44:50 -0700 (PDT)
Message-ID: <>

On Oct 11, 8:52 pm, HansP <> wrote:
> 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
> 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

It sounds to me like the specific SQL at hand benefits from (fresh) column statistics and/or histograms.
Also see The analyze statement can be 'for table' or e.g. 'for table for all columns size 1'.
dbms_stats.gather_table_stats by default does 'FOR ALL COLUMNS SIZE AUTO'. ( d_stats.htm#i1036461)

So I guess this is related to the observation you have made. So how did your colleague gather the stats?

Regards, Erik Received on Sat Oct 11 2008 - 14:44:50 CDT

Original text of this message