Re: good/bad statistics

From: ErikYkema <erik.ykema_at_gmail.com>
Date: Sat, 11 Oct 2008 12:44:50 -0700 (PDT)
Message-ID: <d3a95318-075d-4f2a-9b1d-3318b4c5f0c2@d70g2000hsc.googlegroups.com>


On Oct 11, 8:52 pm, HansP <hans-peter.sl..._at_atosorigin.com> 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 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

It sounds to me like the specific SQL at hand benefits from (fresh) column statistics and/or histograms.
Also see http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2092.htm#i1590494 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'. (http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/ 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