Re: good/bad statistics

From: HansP <hans-peter.sloot_at_atosorigin.com>
Date: Mon, 13 Oct 2008 07:53:52 -0700 (PDT)
Message-ID: <e902af64-c805-4a5a-83ee-1362782bca94@p58g2000hsb.googlegroups.com>


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 Received on Mon Oct 13 2008 - 09:53:52 CDT

Original text of this message