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

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 01 Mar 2004 12:17:59 -0700

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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2004