Re: Performance of DBMS_STATS vs ANALYZE
- From: Reginald.W.Bailey@xxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 19 May 2004 23:23:34 -0500
Try turning on the monitoring of the tables with the ALTER TABLE procedure
. This will gather statistics on tables that have changed more than 10% of
their rows.
- In Oracle9i :
SQL> exec dbms_stats.ALTER_SCHEMA_TAB_MONITORING('<owner>',TRUE);
SQL> exec dbms_stats.ALTER_DATABASE_TAB_MONITORING(TRUE);
RWB
============================================================================================================
Reginald W. Bailey
IBM Global Services
reginald.w.bailey@xxxxxxxxxxxx
baileyre@xxxxxxxxxx
============================================================================================================
charlottejanehammond
@yahoo.com To: oracle-l@xxxxxxxxxxxxx
Sent by: cc:
oracle-l-bounce@free Subject: Performance of
DBMS_STATS vs ANALYZE
lists.org
05/19/2004 12:18 PM
Please respond to
oracle-l
Hi All,
Could I ask people's experience with DBMS_STATS with regard to performance.
When I run this:
DBMS_STATS.GATHER_SCHEMA_STATS(
OWNNAME=>'myschema',
ESTIMATE_PERCENT=>'<<n>>',
CASCADE=>TRUE);
It is anywhere between 3 and 4 times slower than a script containing
ANALYZE TABLE myschema.<table> ESTIMATE STATISTICS SAMPLE <<n>> PERCENT
FOR TABLE FOR ALL INDEXES;
for all the tables in my schema. There are approx. 1000 tables, many of
which are empty.
I have tried this on 9.2.0.4 and 9.2.0.5 with similar results.
Is this typical?
Thanks
- Charlotte
---------------------------------
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
