Re: Performance of DBMS_STATS vs ANALYZE

From: <Reginald.W.Bailey_at_jpmorgan.com>
Date: Wed, 19 May 2004 23:23:34 -0500
Message-ID: <OF5BDAF875.ECC168BA-ON86256E99.00802F9D@chase.com>

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_at_jpmorgan.com
baileyre_at_us.ibm.com

                                                                                                                                    
                    charlottejanehammond                                                                                            
                    @yahoo.com                 To:     oracle-l_at_freelists.org                                                       
                    Sent by:                   cc:                                                                                  
                    oracle-l-bounce_at_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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Wed May 19 2004 - 23:27:12 CDT