Skip to content

ANALYZE vs DBMS_STATS

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:

Features DBMS_STATS ANALYZE
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 type Stored procedure SQL Command
Accuracy More accurate Less accurate
Granularity level Multiple levels The lowest level
Collect Range Broader Narrower
System privileges required ANALYZE ANY and ANALYZE ANY DICTIONARY ANALYZE ANY    

Leave a Reply

Your email address will not be published. Required fields are marked *