| 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
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/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
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
 |
 |