Re: good/bad statistics

From: HansP <hans-peter.sloot_at_atosorigin.com>
Date: Tue, 14 Oct 2008 00:40:06 -0700 (PDT)
Message-ID: <3a70fa88-8565-4dc8-8a7b-8066747f7bb1@j68g2000hsf.googlegroups.com>


On 13 okt, 21:32, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --

Hello Mark,

No the bad version had statistics on all columns. I think the problem was that although there were new statistics on the indexed columns, the old statistics at the unindexed columns were still present.
I removed all the statistics and gathered new but only on the indexed columns.
This was enough to solve the problem.

Regards Hans-Peter Received on Tue Oct 14 2008 - 02:40:06 CDT

Original text of this message