Re: Performance of DBMS_STATS vs ANALYZE
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 May 2004 08:42:06 +0100
It's quite tricky to decide what ANALYZE is doing,
since it uses back-door methods to visit the data,
but if you enable sql_trace for dbms_stats, you will
find that the whole thing is done through SQL. The
extra time could come from:
some of the dictionary-related SQL that checks
for things like 'is this index monitored', 'is this
table monitored'.
some of the dynamic sampling that takes place
on the data dictionary before the above SQL
is executed
the extra tablescans that take place (select count(*))
to check whether to repeat the stats collection query
with a higher sample because the sample used was
unsafe
the 100% compute of statistics on indexes that seems
to take place sometimes even when the sample
percent is only (say) 20.
and so on...
The figure of 3 to 4 is probably not "typical". If your
ANALYZE time is low, then a large fraction of the
extra time may be a fairly static overhead due to the
data dictionary activity. But the extra 'per table' cost
could make a factor of 2 quite likely.
People frequently expend too much effort generating
statistics - if you've got the time window it doesn't usually
do any damage; if you haven't then you need to know
your data better, and avoid gathering statistics too
frequently, or with too high a percentage.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
----- Original Message -----
From: "Charlotte Hammond" <charlottejanehammond@xxxxxxxxx>
To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, May 19, 2004 6:18 PM
Subject: Performance of DBMS_STATS vs ANALYZE
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:
