Re: table stats

Before I run dbms_stats I normally change the sort_area_size for the session to 100M. Will this have an effect on the execution plan that will be generated later? I should not think so but I just need a confirmation.

Daud

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c68163$t71$1_at_titan.btinternet.com>...
> The default statistics collection for dbms_stats
> is NOT to collect histograms. (Connor's
> comment about 'for all columns size 1' is
> correct - but this means no histogram).
>
> If you check user_tab_histograms after a
> default collection, you will find 2 entries
> per column, but this isn't a 'real' histogram,
> it's just a way of representing the low/high.
>
> Possibly your 'takes ages' using dbms_stats
> is because the default for dbms_stats is a
> COMPUTE - try setting estimate_percent
> to a value that matches your analyze.
>
>
> I do have a vague memory of seeing a bug
> report about dbms_stats taking longer than
> the equivalent analyze though - even when
> doing (notionally) the same thing. I don't
> recall any details though.
>
>
> As far as I know, for CBO purposes you can
> mix dbms_stats with analyze perfectly safely.
> The relevant figures are usually identical once you've
> worked out the exact matching set of parameters
> to dbms_stats.
>
> --
> 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.0404212258.282de059_at_posting.google.com...
> > Yes, the default for method_opt is not null. But if I left it as
> > default, it takes ages to complete as it collects too much statistics
> > for histograms and stuff like that. Which option of method_opt is the
> > same as the one from analyze table command?
> >
> > One other question. Why can't I mixed analyze table with dbms_stats?
> > The current tables have been analyzed using 'analyze table ...' but I
> > would slowly like to start using dbms_stats.
> >
> > thanks
> > Daud
Received on Fri Apr 23 2004 - 03:00:48 CDT