Re: dbms_stats

In Oracle 8i you may as well stick with analyze since the dbms_stats call you use translates simply into a

   "analyze table ... ESTIMATE statistics sample 10 percent FOR TABLE FOR ALL INDEXES for all indexed columns size 2"

Why did you go from a simple analyze to gathering histograms on all indexed columns? I question the rationale of gathering histograms of "size 2". Aside from that, I question the rationale of a blanket histogram gathering (regardless of # of buckets) on "all indexed" or "all" columns. Histograms are like medicine. In the right (i.e. sparing dose) they are a therapeutic tool. In the wrong, especially too high dose, they become poison.

At 02:04 PM 12/3/2003, you wrote:
>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

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

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).