Re: table stats

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

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"