RE: What are the implications of running dbms_stats and analyze compute?
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Aug 2005 13:36:36 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mercadante, Thomas F
(LABOR)
Sent: Tuesday, August 09, 2005 1:17 PM
To: achoto@xxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ana Choto
Sent: Tuesday, August 09, 2005 11:53 AM
To: Wolfgang Breitling
Cc: oracle-l@xxxxxxxxxxxxx
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@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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
