Re: method

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 27 Jun 2004 11:10:57 +0100
Message-ID: <001901c45c2f$0de8dec0$7102a8c0@Primary>

Only until you get to version 10g - where the default values change. The default for

estimate_percent
changes from 'compute' to 'pick a number, any number, don't tell me which one' (or AUTO as it is more commonly known).

Every time I have a problem with clients switching from analyze do dbms_stats, I always run through a test to see which values of method_opt do which stats for which version of Oracle - in general this means running up a test case, then checking the stats columns in:

    user_tables
    user_indexes
    user_tab_columns (or user_tab_col_statistics)

and all the partitioned ones if relevant and all the cluster ones if relevant.
The actual values recorded can vary - but the significant point is whether any values are created at all.

method_opt always causes problems when you convert. (That, and forgetting that the default for cascade is FALSE, whilst the default for a simple 'analyze table' is to do indexes too).

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: "Wolfgang Breitling" <breitliw_at_centrexcc.com> To: <oracle-l_at_freelists.org> Sent: Friday, June 25, 2004 11:55 PM Subject: Re: method_opt

Just use the default ( 'for all columns size 1'). For non-partitioned tables

dbms_stats.gather_table_stats(null,'xxx',cascade=>true);

is equivalent to

analyze table xxx compute statistics

At 04:28 PM 6/25/2004, you wrote:
>I don't really understand method_opt usage for dbms_stats. If I issue a
>simple analyze table compute stats, what would be the clause to supply
>method opt to mimic the same sort of analysis as that analyze command?
>I'm not concerned with histogram generation at this point in this test.
>Thanks
>--
>..

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sun Jun 27 2004 - 05:07:51 CDT

Re: method_opt