| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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"
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu May 20 2004 - 19:59:32 CDT
 |
 |