Skip to content

ANALYZE vs DBMS_STATS

  • by
  • October 20, 2012January 23, 2021

Oracle recommends to use DBMS_STATS instead of ANALYZE since 8i. Sometimes, we might get confused by the two similar tools. So I took some times to read several documents and summarize the key differences as below:

FeaturesDBMS_STATSANALYZE
Can gather statistics for CBO
Can validate the structure of a segment
Can list the chained rows
Can collect freelist blocks
Can gather statistics in parallel
Can gather global statistics for partitions
Can be export/import
Can analyze external tables
Can analyze system objects
Command typeStored procedureSQL Command
AccuracyMore accurateLess accurate
Granularity levelMultiple levelsThe lowest level
Collect RangeBroaderNarrower
System privileges requiredANALYZE ANY and ANALYZE ANY DICTIONARYANALYZE ANY    

Leave a Reply

Your email address will not be published.