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

 The analyze command still exists in 10gR1 though the SQL manual does state that you should not use it for collecting optimizer statistics as eventually only statistics generated by dbms_stats will be used by the CBO. The key word is eventually, which may be a decade from now or with the next release.

HTH -- Mark D Powell --

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mercadante, Thomas F (LABOR)
Sent: Tuesday, August 09, 2005 1:17 PM
To: achoto_at_american.edu
Cc: oracle-l_at_freelists.org
Subject: RE: What are the implications of running dbms_stats and analyze compute?

Ana,

AS far as I know, there is not problem with some tables having their stats gather, some by the analyze command, and some by the dbms_stats command. The only difference is that the dbms_stats command has more options.

You should also be aware that the analyze command goes away in Oracle 10g - only dbms_stats command remains. So you should deal with the problems with the dbms_stats command.

I would also take away the analyze command from the freekin developers. You (as the DBA) need to take control of this database. Too many cooks spoil the broth.

You received some great suggestions (read Wolfgang & Mark's emails closely).

You need time to try a couple of things to see what you have to do to get this right. It is fixable, but not when others are changing things back over your head!!

Good Luck!

Tom

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ana Choto Sent: Tuesday, August 09, 2005 11:53 AM
To: Wolfgang Breitling
Cc: oracle-l_at_freelists.org
Subject: 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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l