Re: table stats

Hi,

In 9.2, the av_row_len is also wrong with method_opt=> null.

Dias

Connor McDonald <hamcdc_at_yahoo.co.uk> wrote in message news:<40866233.B5B_at_yahoo.co.uk>...
> Jonathan Lewis wrote:
> >
> > analyze table X estimate statistics;
> >
> > includes limited collection of column-level stats
> > (high, low, num_distinct, num_null)
> >
> > method_opt=>null
> >
> > does not collect ANY column level stats.
> >
> > I run a test case for every method_opt when I upgrade
> > an Oracle version. (And for every variant of ANALYZE)
> > to check which versions of analyze go with which method_opt.
> >
> > Look at stats value in:
> > user_tables
> > user_indexes
> > user_tab_columns
> > user_tab_histograms
> > and their partitioned equivalents.
> >
> > --
> > Regards
> >
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > The Co-operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> > April 2004 Iceland http://www.index.is/oracleday.php
> > June 2004 UK - Optimising Oracle Seminar
> > July 2004 USA West Coast, Optimising Oracle Seminar
> > August 2004 Charlotte NC, Optimising Oracle Seminar
> > September 2004 USA East Coast, Optimising Oracle Seminar
> > September2004 UK - Optimising Oracle Seminar
> >
> > "Daud" <daud11_at_hotmail.com> wrote in message
> > news:f0bf3cc3.0404202225.4145b7f5_at_posting.google.com...
> > > Hi All
> > >
> > > This morning we reloaded two of our tables and then I ran the
> > > dbms_stats.gather_table_stats to collect statistics. The two tables
> > > have pretty much the same the number of records as before (more than 8
> > > mil each).
> > > I found that when I did this it caused the exec plan of some of our
> > > queries to change (to one that was not the best) and performance
> > > degraded by quite a lot.
> > > Then, out of curiousity I collected statictics from the 2 tables
> > > using:
> > > analyze table <mytab> estimate statistics.
> > > After I did that, the exec plan changed to what it was before and
> > > everything went back to normal. Why is this? What is the difference
> > > what method 1 (dbms_stats) and method 2 (analyze). I thought method 1
> > > is the way to go.
> > > The exact command: dbms_stats.gather_table_stats ( ownname => null,
> > > tabname => 'MYTAB', method_opt => null, cascade => true,
> > > estimate_percent => 20).
> > > Oracle version 8.1.7.4 on HP-UX 11.0.
> > >
> > > regards
> > > Daud
>
> I think on 817 the method_opt should be left to the default (which is
> not the same as 'null') to mimic standard analyze behaviour. If memory
> serves its something like 'for all columns size 1'
>
> hth
> connor
Received on Thu Apr 22 2004 - 16:26:38 CDT