Re: good/bad statistics

On 12 okt, 16:31, HansP <hans-peter.sl..._at_atosorigin.com> wrote:
> 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.

Well, then I don't understand your statement about the staleness of dba_tab_col_statistics.
Were there no indexed columns? Received on Sun Oct 12 2008 - 13:30:08 CDT