Re: good/bad statistics

On 14 okt, 15:28, DA Morgan <damor..._at_psoug.org> wrote:
> 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

>

>


> > 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

>

>

Yes I meant it otherwise. Received on Wed Oct 15 2008 - 01:32:20 CDT