联系：手机/微信(+86 17813235971) QQ(107644445)
标题：MON_MODS$和MON_MODS_ALL$统计DML操作次数
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
*_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$表中信息清空