difference between analyze and gather stats
I always wondered, whast the difference between analyze and gather database statistics??? arent both used for performance tuning reasons too?
"High Salaries = Happiness = Project Success."
DBMS_STATS is new introduced in 8i onwards and before that analyze command was used(gather_database statistics is one of the package for dbms_stats). Both are used for cost based optimizer to gather system statistics to optimize your queries. To answer your question, YES it is used for performance tuning purpose, but you can use it too to find information like no of blocks/rows/high water mark etc used by any system/user objects.
Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics. That package lets you collect statistics in parallel
, collect global statistics for partitioned objects, and fine-tune your statistics collection in other ways. Further, the cost-based optimizer will eventually use only statistics that have been collected by DBMS_STATS
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Click Here to Expand Forum to Full Width