Re: Performance of DBMS_STATS vs ANALYZE
- From: Connor McDonald <hamcdc@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 21 May 2004 02:02:32 +0100 (BST)
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 //www.freelists.org/archives/oracle-l/
> FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
