DBMS_STATS allows you transfer stats between servers to give consistant execution plans with differing data.
DBMS_STATS allows you to gather system statistics (9i) to make the decision process more accurate. In 8i stats such as i/o, CPU and memory usage are estimated.
Don't get hung up about it. I still use ANALYZE for on-off things. Alot of people still use DBMS_UTILITY.ANALYZE_SCHEMA. They all do essentially the same thing. You should consider moving to DBMS_STATS since it's Oracle's current recommendation.
Thanks for the notes,
I know analyze is a command and dbms_stats is a packge.
My question is:
beside the desupport for ANALYZE command in the future, and automation in dbms_stats package, are there any major differences ? Why should i use dbms_stats ?
Apart from the fact that there are tons of things that can be done only by using DBMS_STATS, if we concentrate only on the difference between ANALYZE TABLE(INDEX) versus DBMS_STATS.GATHER_TABLE(INDEX)_STATS:
- when you are using ESTIMATE, with DBMS_STATS you can specify wheather you want the specified percent to be gathered by random row sampling or random block sampling; With ANALYZE you have no controll on how sampling for the ESTIMATE will be performed
- on the other hand, with ANALYZE you can choose to estimate based on the specified number of rows, while with DBMS_STATS there is no such option - you can only specify it in a form of percent of total rows
But neither of the above differences is not of major importance IMHO. The announcement that ANALYZE will be dessuported in the future on the other hand is enough strong argument alone to start getting used to it.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
I haven't played with the analyze-type procs from the package yet because my own ANALYZE procs work just fine for me so far
I have my procs set up so they can be broken up into as many calls as you want that run at the same time. So, you can run 5 jobs, each one pulling from the same list of tables (but not overlapping).
So, I can be, in this example, analyzing 5 indexes at the exact same time. Since, from my limited understanding, DBMS_STATS will not analyze an index in parallel, I don't see the proc that analyzes the whole schema as sufficient to replace my existing process. Although I could replace my ANALYZE TABLE calls with the the corresponding package calls, of course.
I found that when I used the Parallel features of dbms_analyze it took longer than without parallel. I, too, wrote my own parallel analyze script where I analyze multiple objects (using dbms_stats) at the same time.