Re: good/bad statistics

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 13 Oct 2008 12:32:05 -0700 (PDT)
Message-ID: <af4014a7-e87b-4a22-9eec-977922186226@u57g2000hsf.googlegroups.com>


On Oct 13, 10:53 am, HansP <hans-peter.sl..._at_atosorigin.com> wrote:
> On 13 okt, 00:17, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > 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 --
>
> @Erik
> Yes there were indexed columns.
>
> @Mark
> My experience with analyze on 8i and higher is that some statistics
> miss when using analyze.
> I have seen performance problems caused by this.
> When tracing with 10053 event it was visible.
> A new analyze with dbms_stats helped to get rid of the problems.
>
> Regards Hans-Peter- Hide quoted text -
>
> - Show quoted text -

The question was not if there were indexed columns it was if unindexed columns were referenced in the where clause. If my reading of the posted information was correct the bad version had generated statistics only on indexed columns. By default analyze generates statistics on all columns so if any of the filter conditions reference unindexed columns then the analyze version of the statistics could have information the dbms_stats version was missing. The fix in this case would be to run dbms_stats set to generate column statistics on all columns.

HTH -- Mark D Powell -- Received on Mon Oct 13 2008 - 14:32:05 CDT

Original text of this message