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
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l