GATHER EMPTY : Gathers statistics only on tables without statistics.

LIST STALE : Creates a list of tables with stale statistics.（通过*_tab_modifications视图）

LIST EMPTY : Creates a list of tables that do not have statistics.

The objlist parameter identifies an output parameter for the LIST STALE and LIST EMPTY options. The objlist parameter is of type DBMS_STATS.OBJECTTAB.

Step 1 : Perform a quick analyze to load in base statistics

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS (
  ownname           => 'scott',
  estimate_percent  => null,              -- Small table, lets compute
  block_sample      => false,
  method_opt        => 'FOR ALL COLUMNS',
  degree            => null,              -- No parallelism used in this example
  granularity       => 'ALL',
  cascade           => true,              -- Make sure we include indexes
  options           => 'GATHER'           -- Gather mode
  );
END;
/

PL/SQL procedure successfully completed.

Step 2 : Examine the current statistics

SELECT table_name, num_rows, blocks, avg_row_len    
FROM user_tables
WHERE table_name='EMP';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
EMP                                  1500         28          92

Step 3 : Turn on Automatic Monitoring

Now turn on automatic monitoring for the emp table. This can be done using the alter table method. Starting with Oracle 9i, you can also perform this at the "schema", and "entire database" level. I provide the syntax for all three methods below.

通过alter table 语句我们可以设置oracle数据库自动监控某张表的变化，从9i开始，我们还可以在schema或者数据库级别设置是否监控数据变化，监控结构会存储在*_tab_modifications视图中。

Monitor only the EMP table.

alter table emp monitoring;

Table altered.

Monitor all of the tables within Scott's schema. (Oracle 9i and higher)

BEGIN
  DBMS_STATS.alter_schema_tab_monitoring('scott', true);
END;
/

PL/SQL procedure successfully completed.

Monitor all of the tables within the database. (Oracle 9i and higher)

Note: Although the option to collect statistics for SYS tables is available via ALTER_DATABASE_TAB_MONITORING, Oracle continues to recommend against this practice until the next major release after 9i Release 2. Also note that the ALTER_DATABASE_TAB_MONITORING procedure in the DBMS_STATS package only monitors tables; there is an ALTER INDEX...MONITORING statement which can be used to monitor indexes. Thanks to Nabil Nawaz for providing this and pointing out an error I made in the previous version of this article.

BEGIN
  DBMS_STATS.alter_database_tab_monitoring (
    monitoring => true,
    sysobjs    => false);      -- Don't set to true, see note above.
END;
/

PL/SQL procedure successfully completed.

Step 4 : Verify that monitoring is turned on.

Note: The results of the following query are from running the alter table ... statement on the emp table only.

可以通过*_tables视图的monitoring字段来判断某张表是否开启了自动监控

SELECT table_name, monitoring
FROM user_tables
ORDER BY monitoring;

TABLE_NAME                     MONITORING
------------------------------ ----------
DEPT                           NO
EMP                            YES

Step 5 : Delete some rows from the database.

SQL> DELETE FROM emp WHERE rownum < 501;

500 rows deleted.

SQL> commit;

Commit complete.

Step 6 : Wait until the monitered data is flushed.