The following is a quick overview of the two.
- Analyze
- The only method available for collecting statistics in Oracle 8.0 and lower.
-
ANALYZE
can only run serially. -
ANALYZE
cannot overwrite or delete certain types of statistics that where generated byDBMS_STATS
. -
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.- For partitioned tables and indexes,
ANALYZE
gathers statistics for the individual partitions and then calculates the global statistics from the partition statistics. - For composite partitioning,
ANALYZE
gathers statistics for the subpartitions and then calculates the partition statistics and global statistics from the subpartition statistics.
- For partitioned tables and indexes,
-
ANALYZE
can gather additional information that is not used by the optimizer, such as information about chained rows and the structural integrity of indexes, tables, and clusters.DBMS_STATS
does not gather this information. - No easy way of knowing which tables or how much data within the tables have changed. The DBA would generally re-analyze all of their tables on a semi-regular basis.
- DBMS_STATS
- Only available for Oracle 8i and higher.
- Statistics can be generated to a statistics table and can then be imported or exported between databases and re-loaded into the data dictionary at any time. This allows the DBA to experiment with various statistics.
-
DBMS_STATS
routines have the option to run via parallel query or operate serially. - Can gather statistics for sub-partitions or partitions.
- Certain DDL commands (ie. create index) automatically generate statistics, therefore eliminating the need to generate statistics explicitly after DDL command.
-
DBMS_STATS
does not generate information about chained rows and the structural integrity of segments. - The DBA can set a particular table, a whole schema or the entire database to be automatically monitored when a modification occurs. When enabled, any change (insert, update, delete, direct load, truncate, etc.) that occurs on a table will be tracked in the SGA. This information is incorporated into the data dictionary by the SMON process at a pre-set interval (every 3 hours in Oracle 8.1.x, and every 15 minutes in Oracle 9i). The information collected by this monitoring can be seen in the
DBA_TAB_MODIFICATIONS
view. Oracle 9i introduced a new function in theDBMS_STATS
package called:FLUSH_DATABASE_MONITORING_INFO
. The DBA can make use of this function to flush the monitored table data more frequently. Oracle 9i will also automatically call this procedure prior to executingDBMS_STATS
for statistics gathering purposes. Note that this function is not included with Oracle 8i. -
DBMS_STATS
provides a more efficient, scalable solution for statistics gathering and should be used over the traditionalANALYZE
command which does not support features such as parallelism and stale statistics collection. - Use of table monitoring in conjunction with
DBMS_STATS
stale object statistics generation is highly recommended for environments with large, random and/or sporadic data changes. These features allow the database to more efficiently determine which tables should be re-analyzed versus the DBA having to force statistics collection for all tables. Including those that have not changed enough to merit a re-scan)