Re: dbms_stats

I think there are various little (hah!) details that change with versions, but unless your analyze command was:

    analyze table T estimate statistics sample 10 percent

        for table
        for indexes
        for all indexed columns size 2

(I may have the sample clause in the wrong place).
then the two commands will be generating different things. The default for analyze columns is 'size' 75, which gives Oracle much better precision on range scans with literal values than 'size 2' - which may be a significant part of your problem.

Regards

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

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November

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

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, December 03, 2003 9:04 PM

Hello,

Oracle 8.1.7.4 on HP-UX 11i

A week ago, we replaced 'analyze table ... estimate statistics sample 10 percent' with dbms_stats.gather_schema_stats('x', estimate_percent=>10, cascade=>true, degree=>4, method_opt=>''for all indexed columns size 2')

Performace is good against partitioned tables but not for non-partitioned tables.

Saw a note in Metalink that its better to do the above with 'cascade=>false' and then do a gather_index_stats separately.

In my tests, I see that 'analyze' makes the CBO use an index while dbms_stats is making the CBO to use a FTS instead.

Have you faced any similar issues?

TIA
Prakash

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).