yeah good idea. how do you determine which columns need histograms? I have 100s of tables in my applications and I do not have time to determine it. I typically have them run over night. ----- Original Message ----- From: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Monday, March 01, 2004 2:17 PM Subject: Re: RE: query slow in 9i, but not slow in 8i > 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='FO R > >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 > ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------