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>
>>>>>>>> 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
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.orgReceived on Tue Oct 14 2008 - 08:28:53 CDT