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
