Re: good/bad statistics

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 14 Oct 2008 06:28:53 -0700
Message-ID: <1223990929.315490@bubbleator.drizzle.com>


HansP wrote:

> On 13 okt, 19:26, DA Morgan <damor..._at_psoug.org> wrote:
>> HansP 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
>> 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
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
> 
> Hello Daniel,
> 
> I know that dbms_stats is deprecated and I never use analyze myself
> anymore.
> But from time to time I encounter old scripts that are used to
> generate statistics with the analyze command or applications that
> have
> the analyze command in its code.
> 
> So sometimes there are statistics of unkown origin.

You got what I wrote 180 degrees reversed. ANALYZE is deprecated. DBMS_STATS is current.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Oct 14 2008 - 08:28:53 CDT

Original text of this message