Re: good/bad statistics

From: HansP <hans-peter.sloot_at_atosorigin.com>
Date: Sun, 12 Oct 2008 07:31:29 -0700 (PDT)
Message-ID: <c270b873-f912-4013-bf32-bd93a5ff4854@v30g2000hsa.googlegroups.com>


On 11 okt, 21:44, ErikYkema <erik.yk..._at_gmail.com> wrote:
> 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 seehttp://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/sta...
> 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

He did a dbms_stats with for all indexed columns. Received on Sun Oct 12 2008 - 09:31:29 CDT

Original text of this message