Re: What are the implications of running dbms_stats and analyze compute?

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_at_centrex                                          To 
             cc.com>                   achoto_at_american.edu                 
                                                                        cc 
             08/09/2005 11:41          oracle-l_at_freelists.org              
             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
>
> --
> http://www.freelists.org/webpage/oracle-l
>

--
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com


--
http://www.freelists.org/webpage/oracle-l