oracle set statistics,compute statistics 和estimate statistics???

Analyze vs. DBMS_STATS

The following is a quick overview of the two.

•        Analyze

o        The only method available for collecting statistics in Oracle 8.0 and lower.

o        ANALYZE can only run serially.

o        ANALYZE cannot overwrite or delete certain types of statistics that where generated by DBMS_STATS.

o        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.

o        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.

o        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

o        Only available for Oracle 8i and higher.

o        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.

o        DBMS_STATS routines have the option to run via parallel query or operate serially.

o        Can gather statistics for sub-partitions or partitions.

o        Certain DDL commands (ie. create index) automatically generate statistics, therefore eliminating the need to generate statistics explicitly after DDL command.

o        DBMS_STATS does not generate information about chained rows and the structural integrity of segments.

o        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 the DBMS_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 executing DBMS_STATS for statistics gathering purposes. Note that this function is not included with Oracle 8i.

o        DBMS_STATS provides a more efficient, scalable solution for statistics gathering and should be used over the traditional ANALYZE command which does not support features such as parallelism and stale statistics collection.

o        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)