Re: good/bad statistics

From: DA Morgan <>
Date: Mon, 13 Oct 2008 10:26:55 -0700
Message-ID: <>

HansP wrote:

> On 13 okt, 00:17, Mark D Powell <> wrote:

>> On Oct 12, 2:30 pm, ErikYkema <> wrote:
>>> On 12 okt, 16:31, HansP <> wrote:
>>>> On 11 okt, 21:44, ErikYkema <> wrote:
>>>>> On Oct 11, 8:52 pm, HansP <> 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
>>>>>> 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 see
>>>>> 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'. (
>>>>> 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

It isn't a question of missing. It is a question of reading the docs and understanding that Oracle has deprecated ANALYZE. All optimizer related statistics must be generated with DBMS_STATS.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Mon Oct 13 2008 - 12:26:55 CDT

Original text of this message