Re: good/bad statistics

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sun, 12 Oct 2008 15:17:31 -0700 (PDT)
Message-ID: <18aa02eb-73e9-417b-bc29-151c249718eb@l76g2000hse.googlegroups.com>


On Oct 12, 2:30 pm, ErikYkema <erik.yk..._at_gmail.com> wrote:
> 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?- Hide quoted text -
>
> - Show quoted text -

There are queries where the column statistics for non-indexed columns can be important to the CBO. If you use the more common form of the analyze command then by default you are generating column statistics for all columns. If the queries in question contain where clause conditions that use non-indexed columned compared to a value this is one possibility.

On at least some versions of 9.2 the dbms_stats package does not compute the average row size correctly when the row contains a LOB. Analyze does a better job in this case. This is another case where you may see a difference between the statistics generated by analyze compared to dbms_stats.

HTH -- Mark D Powell -- Received on Sun Oct 12 2008 - 17:17:31 CDT

Original text of this message