user_tab_modifications學習

To recognize whether object statistics are stale, the database engine counts the number of rows modified
through SQL statements for each object. The result of that counting is externalized through the data dictionary
views all_tab_modifications, dba_tab_modifications, and user_tab_modifications. The
following query is an example:
SQL> SELECT inserts, updates, deletes, truncated
2 FROM user_tab_modifications
3 WHERE table_name = 'T';

INSERTS UPDATES DELETES TRUNCATED
---------- ---------- ---------- ----------
775 16636 66 NO
Based on this information, the package dbms_stats is able to determine whether the statistics associated
with a specific object are stale. Up to Oracle Database 10g, the statistics are considered stale if at least
10 percent of the rows have been modified. As of Oracle Database 11g, you can configure the threshold through the
parameter stale_percent. Its default value is 10 percent. Later in this chapter, the section “Configuring the
Package dbms_stats: The 11g Way” will show how to change it.
In Oracle9i, counting is enabled only when it is explicitly specified at the table level. Concretely, this is
carried out by specifying the option monitoring through the CREATE TABLE or ALTER TABLE statement.
To enable it easily for a whole schema, or even for the whole database, the package dbms_stats provides the
procedures alter_schema_tab_monitoring and alter_database_tab_monitoring, respectively. Note
that these procedures just execute an ALTER TABLE statement on all available tables. In other words, the
setting has no impact on tables created after their execution.
As of Oracle Database 10g, the option monitoring is deprecated. Counting is controlled databasewide
by the initialization parameter statistics_level. If it is set to either typical (which is the default value)
or all, counting is enabled.

設定:SQL>  exec dbms_stats.set_table_prefs('APOLLO','EMP','STALE_PERCENT',1);
修改為 1％. 範圍從 1-100. 
恢復:
SQL> exec dbms_stats.set_table_prefs('APOLLO','EMP','STALE_PERCENT',null);
查詢:
SQL>  select dbms_stats.get_prefs('STALE_PERCENT','APOLLO','EMP') A from dual;

A
------------------------------
10