Re: RE: query slow in 9i, but not slow in 8i

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 01 Mar 2004 12:17:59 -0700
Message-Id: <6.0.3.0.2.20040301121421.02b56190@pop.centrexcc.com>

I would NOT do that. "method_opt='FOR ALL COLUMNS'" will collect histograms of (default) size 75 for ALL columns of the table. At best that would be a waste of time and resources, but it could easily be rather detrimental to access paths. You do NOT want to collect histograms unless it is warranted.

If the columns did not have histograms in 8i, I would not suddenly create any in 9i.

At 11:55 AM 3/1/2004, you wrote:
>then you did not properly analyze the tables. look at the cardinality in
>the plan.
>
>card=estimated rows. estimated runs is determined by dba_tables.num_rows
>which is populated when tables are analyzed.
>
>do the following
>
>exec
>dbms_stats_gather_table_stats(ownname=>'blah',tabnam=>'blah',method_opt='FOR
>ALL COLUMNS',cascade=>true);
>
>on both tables in 9204.

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.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 Mon Mar 01 2004 - 13:15:15 CST