Re: What are the implications of running dbms_stats and analyze compute?
- From: Ana Choto <achoto@xxxxxxxxxxxx>
- To: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- Date: Tue, 9 Aug 2005 11:52:34 -0400
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
Other related posts:
