#object统计信息的时效性(所谓的过时的统计信息)
关于*_tab_modifications
Columns
   ___________________________
 
   TABLE_OWNER
      Owner of modified table
   TABLE_NAME
      Modified table
   PARTITION_NAME
      Modified partition
   SUBPARTITION_NAME
      Modified subpartition
   INSERTS
      Approximate number of rows inserted since last analyze
   UPDATES
      Approximate number of rows updated since last analyze
   DELETES
      Approximate number of rows deleted since last analyze
   TIMESTAMP
      Timestamp of last time this row was modified*********************
   TRUNCATED
      Was this object truncated since the last analyze

Oracle uses these views to identify tables with stale statistics.
Whenever there is 10% change in data in a table, Oracle considers its
statistics to be stale.

oracle 用记录在此表中的信息 来决定统计信息的时效性，当sql执行时oracle查看此表看修改表数据的行数，10G 超过10%就算过期了dbms_stats就用这个判断(11g可以修改这个阀值)

In 10G, the MONITORING and NOMONITORING keywords have been deprecated.
If you do specify these keywords, they are ignored.

In 10g, table-monitoring feature is controlled by the STATISTICS_LEVEL
parameter.

When STATISTICS_LEVEL is set to BASIC, monitoring is disabled on the table.

When STATISTICS_LEVEL is set to TYPICAL, then monitoring is enabled.

10g statistics_level=typical时or all时 启动统计
所以10g,11g是自动启用的

SQL> select MONITORING from user_tables where rownum<2;

MON
---
YES

9i比较麻烦 需要明确指定起用create table ,alert table中monitoring 才行 *_tables中字段MONITORING 或nomonitoring禁用
#dbms_stats中alert_schema_tab_monitoring，alter_database_tab_monitoring也可以控制schema级别和db级所有可用的表是否monitoring（实际就是执行了一个alert table xx monitoring语句）

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE
operations for the table since the last time statistics were gathered.
information about how many rows are affected is maintained in the SGA,
until periodically (about every 15 minutes) SMON flush the data into the data
dictionary.
可以看到oracle说是15分钟smon 执行刷新操作

SQL> create user tt identified by a123
  2  ;

User created.

SQL> grant dba to tt;

Grant succeeded.

SQL> conn tt/a123
Connected.
SQL> create table t_stat ( a int);

Table created.

SQL> declare
  2  begin
  3  for i in 1..1000 loop
  4  insert into t_stat values(i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> set linesize 1000
SQL> select * from user_tab_statistics;

TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION SUBPARTITION_NAME              SUBPARTITION_POSITION OBJECT_TYPE    NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANAL GLO USE STATT STA
------------------------------ ------------------------------ ------------------ ------------------------------ --------------------- ------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ----------------- ------------------- ----------- --------- --- --- ----- ---
T_STAT                                                                                                                                TABLE                              NO  NO

可以看到没有统计信息

SQL> execute dbms_stats.gather_table_stats('TT','T_STAT');

PL/SQL procedure successfully completed.

SQL> select * from user_tab_statistics;

TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION SUBPARTITION_NAME              SUBPARTITION_POSITION OBJECT_TYPE    NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANAL GLO USE STATT STA
------------------------------ ------------------------------ ------------------ ------------------------------ --------------------- ------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ----------------- ------------------- ----------- --------- --- --- ----- ---
T_STAT                                                                                                                                TABLE              1000              0           0          0           3                         0                   0                                              1000 27-APR-10 YES NO        NO

SQL> delete from t_stat;

1000 rows deleted.

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> SQL> select * from user_tab_modifications;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
T_STAT                                                                                                0          0       1000 27-APR-10 NO              0

 set serveroutput on
  DECLARE
     l_objlist dbms_stats.objecttab;
     l_index PLS_INTEGER;
     BEGIN
     dbms_stats.gather_schema_stats(ownname => 'XH',
 objlist => l_objlist,options=>'list stale');
        l_index := l_objlist.FIRST;
     WHILE l_index IS NOT NULL
     LOOP
    dbms_output.put(l_objlist(l_index).ownname || '.');
    dbms_output.put_line(l_objlist(l_index).objname);
    l_index := l_objlist.next(l_index);
    END LOOP;
    END;
   /

#可以看到 查到了统计信息过期的信息(但并不收集解决)
SQL>  set serveroutput on
  DECLARE
SQL>   2       l_objlist dbms_stats.objecttab;
  3       l_index PLS_INTEGER;
  4       BEGIN
  5       dbms_stats.gather_schema_stats(ownname => 'TT',
  6   bjlist => l_objlist,options=>'list stale');
  7          l_index := l_objlist.FIRST;
  8       WHILE l_index IS NOT NULL
  9       LOOP
 10      dbms_output.put(l_objlist(l_index).ownname || '.');
 11      dbms_output.put_line(l_objlist(l_index).objname);
 12      l_index := l_objlist.next(l_index);
 13      END LOOP;
 14      END;
 15     /
TT.T_STAT

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
T_STAT                                                                                                0          0       1000 27-APR-10 NO              0

execute DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'TT', PTIONS=>'gather stale')

SQL> execute DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'TT', PTIONS=>'gather stale')

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications;

no rows selected

SQL>  declare
  2      begin
  3      for i in 1..1000 loop
  4      insert into t_stat values(i);
  5      end loop;
  6      commit;
  7      end;
  8     
  9 
 10  /

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications;

no rows selected

SQL> SQL> delete t_stat where a>999;

1 row deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
T_STAT                                                                                                1          0          1 27-APR-10 NO              0

SQL>  set serveroutput on
SQL>   DECLARE
  2       l_objlist dbms_stats.objecttab;
  3       l_index PLS_INTEGER;
  4       BEGIN
  5       dbms_stats.gather_schema_stats(ownname => 'TT',
  6   bjlist => l_objlist,options=>'list stale');
  7          l_index := l_objlist.FIRST;
  8       WHILE l_index IS NOT NULL
  9       LOOP
 10      dbms_output.put(l_objlist(l_index).ownname || '.');
 11      dbms_output.put_line(l_objlist(l_index).objname);
 12      l_index := l_objlist.next(l_index);
 13      END LOOP;
 14      END;
 15     /

PL/SQL procedure successfully completed.

SQL>
#只修改了1行 不会失效的

SQL> select dbms_stats.get_param(pname=>'stale_percent') from dual;

DBMS_STATS.GET_PARAM(PNAME=>'STALE_PERCENT')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10
默认是10%就 过时效(INSERTS + UPDATES + DELETES) >= 10%

SQL> execute dbms_stats.set_param(pname=>'stale_percent',pval=>2);修改参数（2%就过时效,只有11g才可以修改这个时效阀值）

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param(pname=>'stale_percent') from dual;

DBMS_STATS.GET_PARAM(PNAME=>'STALE_PERCENT')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2

SQL> delete t_stat where a>=970;

30 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from user_tab_modifications;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
T_STAT                                                                                                1          0          1 27-APR-10 NO              0

SQL> SQL> SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();(手动让smon搞)

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
T_STAT                                                                                                1          0         31 27-APR-10 NO              0

SQL>  set serveroutput on
  DECLARE
     l_objlist dbms_stats.objecttab;
     l_index PLS_INTEGER;
     BEGIN
     dbms_stats.gather_schema_stats(ownname => 'TT',
SQL>  bjlist => l_objlist,options=>'list stale');
        l_index := l_objlist.FIRST;
  2    3    4    5    6    7    8       WHILE l_index IS NOT NULL
  9       LOOP
 10      dbms_output.put(l_objlist(l_index).ownname || '.');
 11      dbms_output.put_line(l_objlist(l_index).objname);
 12      l_index := l_objlist.next(l_index);
 13      END LOOP;
 14      END;
 15     /
TT.T_STAT

PL/SQL procedure successfully completed.

可以看到 过时效了

SQL> execute dbms_stats.reset_param_defaults;(使其回到默认值，all)

PL/SQL procedure successfully completed.

SQL> execute dbms_output.put_line(dbms_stats.get_param(pname=>'stale_percent'));
10

PL/SQL procedure successfully completed.

select u.TIMESTAMP,
           t.last_analyzed,
           u.table_name,
           u.inserts,
           u.updates,
           u.deletes,
           d.num_rows,
           TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
percent
    from user_tables t,USER_TAB_MODIFICATIONS u,dba_tables d
    where u.table_name = t.table_name
    and d.table_name = t.table_name
    and d.owner = 'TT'
    and (u.inserts > 10000 or u.updates > 10000 or u.deletes > 10000)
  order by t.last_analyzed
/

Since in 10g CBO is used, having up to date statistics is so important to generate
good execution plans. Automatic statistics collection job using DBMS_STATS
package depends on the monitoring data to determine when to collect statistics
on objects with stale objects.

Stats are considered stale when #(INSERTS + UPDATES + DELETES) >= 10%
 of NUM_ROWS from dba_tables: