RE: What are the implications of running dbms_stats and analyze compute?
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <achoto@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Aug 2005 11:47:54 -0400
The default analyze command does not build histograms. Were you using
histograms in the prior version? If not change SIZE 200 to SIZE 1.
This would eliminate changes in plans due to histograms from the
equation.
There were at least a dozen hidden parameters that affect the CBO which
had their default value changed from off to on with version 9. One of
them might be causing you a problem depending on the type of SQL
statements involved.
I suggest you look for and attempt to tune the affected SQL. If you see
a pattern in the types of SQL statements affects you can contact support
as your site might be a candidate for switching a specific underbar
parameter back off.
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ana Choto
Sent: Tuesday, August 09, 2005 11:24 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: What are the implications of running dbms_stats and analyze
compute?
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
