Re: Performance of DBMS_STATS vs ANALYZE

DBMS_STATS typically runs a little slower, but in your particular instance, one 
reason is that
you're asking dbms_stats to do a lot more than analyze - example below

SQL> create table T as
  2  select * from all_objects;

Table created.

SQL> create index T_X on T ( object_id);

Index created.

SQL> ANALYZE TABLE T  estimate STATISTICS
  2  SAMPLE 20 PERCENT
  3  FOR TABLE
  4  FOR ALL INDEXES;

Table analyzed.

SQL> create table T1 as
  2  select * from all_objects;

Table created.

SQL> create index T1_X on T1 ( object_id);

Index created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>user,tabname=>'T1', -
>           ESTIMATE_PERCENT=>20,CASCADE=>TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.99
SQL> @tab
Enter value for table_name: t

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN  
CHAIN_CNT
------------------------------ ---------- ---------- ------------ ----------- 
----------
T                                   31738        430            1          96   
       0
T1                                  32135        430            0          92   
       0

SQL> @tabcol
Enter value for table_name_req: t

COLUMN_NAME                    NUM_DISTINCT    DENSITY AVG_COL_LEN  NUM_NULLS   
HIST_CNT
------------------------------ ------------ ---------- ----------- ---------- 
----------
OWNER                                                                           
       0
OBJECT_NAME                                                                     
       0
SUBOBJECT_NAME                                                                  
       0
OBJECT_ID                                                                       
       0
DATA_OBJECT_ID                                                                  
       0
OBJECT_TYPE                                                                     
       0
CREATED                                                                         
       0
LAST_DDL_TIME                                                                   
       0
TIMESTAMP                                                                       
       0
STATUS                                                                          
       0
TEMPORARY                                                                       
       0
GENERATED                                                                       
       0
SECONDARY                                                                       
       0

13 rows selected.

SQL> @tabcol
Enter value for table_name_req: t1

COLUMN_NAME                    NUM_DISTINCT    DENSITY AVG_COL_LEN  NUM_NULLS   
HIST_CNT
------------------------------ ------------ ---------- ----------- ---------- 
----------
OWNER                                    16      .0625           6          0   
       2
OBJECT_NAME                           17669 .000056596          24          0   
       2
SUBOBJECT_NAME                            1          1           2      32060   
       2
OBJECT_ID                             32135 .000031119           5          0   
       2
DATA_OBJECT_ID                         2644 .000378215           2      29400   
       2
OBJECT_TYPE                              25        .04           9          0   
       2
CREATED                                2579 .000387747           8          0   
       2
LAST_DDL_TIME                          2246 .000445236           8          0   
       2
TIMESTAMP                              2476 .000403877          20          0   
       2
STATUS                                    2         .5           7          0   
       2
TEMPORARY                                 2         .5           2          0   
       2
GENERATED                                 2         .5           2          0   
       2
SECONDARY                                 2         .5           2          0   
       2

Notice that your analyze command did not calculate column stats, but the 
dbms_stats did.  You'll
get a "fairer" assessment if you analyze command is:

ANALYZE TABLE myschema.<table> ESTIMATE STATISTICS SAMPLE <<n>> PERCENT 
FOR TABLE FOR ALL INDEXES
for all columns size 1;

hth
connor

 --- Charlotte Hammond <charlottejanehammond@xxxxxxxxx> wrote: > 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 http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> ----------------------------------------------------------------- 

=====
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, 
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------


        
        
                
____________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping" 
your friends today! Download Messenger Now 
http://uk.messenger.yahoo.com/download/index.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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: