*_TAB_MODIFICATIONS视图是用来记录自从收集统计信息后的DML(包括truncate)操作的次数,通过试验分析数据库是如何实现该功能,并且应用该功能来实现数据库自动收集功能(表变化比例决定是否收集统计信息)

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> DESC DBA_TAB_MODIFICATIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER                                        VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 SUBPARTITION_NAME                                  VARCHAR2(30)
 INSERTS                                            NUMBER
 UPDATES                                            NUMBER
 DELETES                                            NUMBER
 TIMESTAMP                                          DATE
 TRUNCATED                                          VARCHAR2(3)
 DROP_SEGMENTS                                      NUMBER
SQL> select text from dba_views where view_name='DBA_TAB_MODIFICATIONS';
TEXT
--------------------------------------------------------------------------------
select u.name, o.name, null, null,
       m.inserts, m.updates, m.deletes, m.timestamp,
       decode(bitand(m.flags,1),1,'YES','NO'),
       m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name, o.name, o.subname, null,
       m.inserts, m.updates, m.deletes, m.timestamp,
       decode(bitand(m.flags,1),1,'YES','NO'),
       m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19
union all
select u.name, o.name, o2.subname, o.subname,
       m.inserts, m.updates, m.deletes, m.timestamp,
       decode(bitand(m.flags,1),1,'YES','NO'),
       m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,
     sys.user$ u
where o.obj# = m.obj# and o.owner# = u.user# and
      o.obj# = tsp.obj# and o2.obj# = tsp.pobj#

这里很清楚，通过union all关联了三个select 语句,分别是查询普通表,分区表,子分区表,这里也可以看出来

对应基表

SQL> desc sys.mon_mods_all$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                               NUMBER
 INSERTS                                            NUMBER
 UPDATES                                            NUMBER
 DELETES                                            NUMBER
 TIMESTAMP                                          DATE
 FLAGS                                              NUMBER
 DROP_SEGMENTS                                      NUMBER
SQL> desc sys.mon_mods$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                               NUMBER
 INSERTS                                            NUMBER
 UPDATES                                            NUMBER
 DELETES                                            NUMBER
 TIMESTAMP                                          DATE
 FLAGS                                              NUMBER
 DROP_SEGMENTS                                      NUMBER

这两个基表结构完全相同,通过收集信息dml操作MON_MODS$然后记录MON_MODS_ALL$中

测试MON_MODS$和MON_MODS_ALL$关系

SQL> SELECT obj#,INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE obj#=69900;
      OBJ#    INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ---------- ------------------- -------------
     69900          0        297          0          0 2013-05-03 01:35:56             0
SQL> !date
Fri May  3 01:51:08 CST 2013
SQL> SELECT obj#,INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE obj#=69900;
      OBJ#    INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ---------- ------------------- -------------
     69900          0        297          0          0 2013-05-03 01:35:56             0
--15分钟未完全刷新mon_mod$
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT obj#,INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE obj#=69900;
no rows selected
SQL> SELECT obj#,INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE obj#=69900;
      OBJ#    INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ---------- ------------------- -------------
     69900          0        323          0          0 2013-05-03 01:54:18             0

这里测试证明DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO实现MON_MODS$刷新到MON_MODS_ALL$,但是未完全刷新MON_MODS$

测试MON_MODS_ALL$

SQL> create table t_xifenfei
  2  as
  3  select object_id,object_name from dba_objects;
Table created.
SQL> select object_id from user_objects where object_name='T_XIFENFEI';
 OBJECT_ID
----------
     76703
SQL> SELECT COUNT(*) FROM T_XIFENFEI;
  COUNT(*)
----------
     74806
--MON_MODS$无数据
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE OBJ#=76703;
no rows selected
--MON_MODS_ALL$中无数据
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
no rows selected
--UPDATE 操作
SQL> UPDATE T_XIFENFEI SET OBJECT_NAME='WWW.XIFENFEI.COM' WHERE MOD(OBJECT_ID,10)=0;
7474 rows updated.
SQL> COMMIT;
Commit complete.
--MON_MODS$和MON_MODS_ALL$中无数据，因为未从内存中刷新到MON_MODS$
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE OBJ#=76703;
no rows selected
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
no rows selected
--执行DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO操作
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
--MON_MODS_ALL$中有数据
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0       7474          0          0 2013-02-12 09:02:05             0
--这里统计的update数据和实际更新条数一致
--MON_MODS$中无数据，因为FLUSH_DATABASE_MONITORING_INFO刷新SGA中的dml和MON_MODS$到MON_MODS_ALL$中，并清空MON_MODS$
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS$ WHERE OBJ#=76703;
no rows selected
--DELETE操作
----session 1
SQL>  DELETE FROM T_XIFENFEI WHERE MOD(OBJECT_ID,3)=2;
24940 rows deleted.
----session 2
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
----session 1
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0       7474      24940          0 2013-02-12 09:04:15             0
------这里可以看到，未提交的DML操作也统计到MON_MODS_ALL$中
----session 1
SQL> rollback;
Rollback complete.
----session 2
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
----session 2
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0       7474      24940          0 2013-02-12 09:04:15             0
------通过这里可以rollback 操作也不能回滚MON_MODS_ALL$中未提交的事务
--再次DELETE操作
SQL> DELETE FROM T_XIFENFEI WHERE MOD(OBJECT_ID,5)=1;
14954 rows deleted.
----session 2
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
----session 1
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0       7474      39894          0 2013-02-12 09:05:54             0
------DELETE操作在MON_MODS_ALL$中累加
--INSERT操作
SQL> insert into t_xifenfei select object_id,object_name from dba_objects;
74806 rows created.
SQL> commit;
Commit complete.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
     74806       7474      39894          0 2013-02-12 09:07:51             0
--收集统计信息
SQL> EXEC DBMS_STATS.gather_table_stats('CHF','T_XIFENFEI');
PL/SQL procedure successfully completed.
--MON_MODS_ALL$数据被清空
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
no rows selected
--TRUNCATE表被清空
SQL> truncate table t_xifenfei;
Table truncated.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> SELECT INSERTS,UPDATES,DELETES,FLAGS,TO_CHAR(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') LAST_TIME,
  2  DROP_SEGMENTS FROM sys.MON_MODS_ALL$ WHERE OBJ#=76703;
   INSERTS    UPDATES    DELETES      FLAGS LAST_TIME           DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- -------------
         0          0     134658          1 2013-02-12 09:29:49             0
----DELETES和FLAGS记录因为truncate操作而发生改变

从10GR2开始,数据库每15分钟就要把数据库的DML操作写入到mon_mods$(从SGA中写入到mon_mod$),但是这个写入过程1分钟,因此可能不是全部记录所有DML操作到mon_mods$.默认情况下,数据库每天会写入SGA中表的DML操作和mon_mods$到mon_mods_all$,也可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO来人工写入到mon_mods_all$中,收集统计信息后mon_mods_all$表中信息清空