This is the option I have run that have also helped. DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => <'owner'>, TABNAME => <'table_name'>, CASCADE => TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1'); But, the developers insist on re-analyzing with analyze compute some of the tables, but then not all of them are analyzed this way, the daily job is running with dbms_stats as follows for all datawarehouse schemas. exec dbms_stats.gather_schema_stats(ownname=>'<OWNER>',options=>'GATHER AUTO',degree=>8,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1'); My question is, if there is a problem by having tables analyzed with dbms_stats and others with analyze compute. Thanks Ana E. Choto American University e-Operations - Information Technology Phone (202) 885-2275 Fax (202) 885-2224 Wolfgang Breitling <breitliw@centrex To cc.com> achoto@xxxxxxxxxxxx cc 08/09/2005 11:41 oracle-l@xxxxxxxxxxxxx AM Subject Re: What are the implications of running dbms_stats and analyze compute? Try DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => <'owner'>, TABNAME => <'table_name'>, CASCADE => TRUE); i.e. without the histograms on every column. That is equivalent to the analyze table xxx compute statistics; Ana Choto wrote: > We have migrated our datawarehouse to 9.2.0.6 from 8.1.7.4. Since the > upgrade we experienced some performance degradation. We run a daily job to > analyze the datawarehouse schemas using dbms_stats. But, queries that ran > in seconds were taking hours to run. So, the developers started > reanalyzing the tables with 'analyze compute', which resulted on the > queries running at the same level they were on 8i. > > The only thing, is that now, some tables have been analyzed with dbms_stats > and others with 'analyze compute'. Is there a problem by doing this? > > Oracle support asked me to run the dbms_stats job with the 'exec > DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => <'owner'>, TABNAME => > <'table_name'> , CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE > 200');' command. But this didn't help, so the developers just reanalyze > the tables with 'analyze compute' command. > > Thanks > > Ana E. Choto > American University > e-Operations - Information Technology > Phone (202) 885-2275 > Fax (202) 885-2224 > > -- > //www.freelists.org/webpage/oracle-l > -- Regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com -- //www.freelists.org/webpage/oracle-l