【效能優化】ANALYZE 與DBMS_STATS的區別

From asktom
-------------
you can import/export/set statistics directly with dbms_stats

it is easier to automate with dbms_stats (it is procedural, analyze is just a command)

dbms_stats is the stated, preferred method of collecting statisttics.

dbms_stats can analyze external tables, analyze cannot.

DBMS_STATS gathers statistics only for cost-based optimization; it does not gather other
statistics. For example, the table statistics gathered by DBMS_STATS include the number
of rows, number of blocks currently containing data, and average row length but not the
number of chained rows, average free space, or number of unused data blocks.

dbms_stats (in 9i) can gather system stats (new)

ANALYZE calculates global statistics for partitioned tables and indexes instead
of gathering them directly. This can lead to inaccuracies for some statistics, such as
the number of distinct values.  DBMS_Stats won't do that.

Most importantly, in the future, ANALYZE will not collect statistics needed by
the cost-based optimizer.