【DB笔试面试650】在Oracle中，如何查询表的DML操作数据变化量？
【DB笔试面试650】在Oracle中，如何查询表的DML操作数据变化量？
题目部分
在Oracle中，如何查询表的DML操作数据变化量？
♣
答案部分
DBA_TAB_MODIFICATIONS视图（基表为SYS.MON_MODS_ALL$）记录了从上次收集统计信息以来表中DML操作变化的数据量，包括执行INSERT、UPDATE和DELETE影响的行数，以及是否执行过TRUNCATE操作。另外，DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO可以将内存（SGA）中的数据快速刷新到数据字典SYS.MON_MODS_ALL$中。
其实，SYS.MON_MODS_ALL$只是最终表，中间还有一个过渡表SYS.MON_MODS$，这两个表的结构是完全相同的。从Oracle 10g开始，当初始化参数STATISTICS_LEVEL的值被设置为TYPICAL或ALL时，默认会启用Oracle中表监控的特性，此时，Oracle会默认监控表上的自上一次分析（Last Analyzed）之后发生的INSERT、UPDATE、DELETE以及表是否被TRUNCATE截断操作，并且Oracle数据库的SMON后台进程每15分钟会将这些操作数量的近似值（内存SGA中记录的DML操作）写入到数据字典基表MON_MODS$中（从SGA中写入到MON_MOD$），但是这个写入过程只持续1分钟，因此，可能不是所有DML操作都会记录到MON_MODS$表中。在默认情况下，数据库每天会将SGA中表的DML操作和MON_MODS$表的数据合并（MERGE）到MON_MODS_ALL$中，也可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO来手动写入到MON_MODS_ALL$中。在收集统计信息后，MON_MODS_ALL$表中信息将被清空。需要注意的是，在作者实际测试过程中发现，Oracle并不是严格按照每15分钟将SGA中的DML刷新到MON_MODS$表中，而且也不是严格按照每天1次的规律刷新MON_MODS$表的数据到MON_MODS_ALL$中。所以，DBA只需要知道，DML数据是SMON进程从SGA中刷新到SYS.MON_MODS$中，然后按照一定的时间规则刷新到SYS.MON_MODS_ALL$表中即可。
在Oracle 10g以前可以使用MONITORING和NOMONITORING这两个选项来控制表级别的监控是否被开启（ALTER TABLE ... MONITORING），此外还可以通过DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING存储过程在SCHEMA级别开启MONITORING，但是从Oracle 10g开始这些方法不再有效，MONITORING和NOMONITORING选项被废弃，其原有功能被STATISTICS_LEVEL参数所覆盖。虽然语句“ALTER TABLE TB_NAME NOMONITORING;”可以执行，但是，执行后表TB_NAME的MONITORING属性依然为YES。表的MONITORING特性现在完全由STATISTICS_LEVEL参数所控制：当STATISTICS_LEVEL设置为BASIC时，表的MONITORING将被禁用；当STATISTICS_LEVEL设置为TYPICAL或ALL时，表的MONITORING特性将被启用。所以，可以通过设置参数STATISTICS_LEVEL为BASIC来禁止SMON后台进程收集DML的操作统计数据。但是，如果参数STATISTICS_LEVEL设置为BASIC，那么Oracle很多功能将不能使用，例如AMM、ASMM、AWR、ASH和ADDM等。因此，在一般情况下，并不建议修改该参数的值。
在Oracle 10g之前，建表之后默认为NOMONITORING，从Oracle 10g开始，建表之后默认为MONITORING。
[oracle@rhel6lhr env_oracle]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 19 13:49:38 2017
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SYS@ora10g> CREATE TABLE TEST_MON_MODS2 AS SELECT * FROM USER_TABLES;
Table created.
SYS@ora10g> ALTER TABLE TEST_MON_MODS2 NOMONITORING;
Table altered.
SYS@ora10g>
SYS@ora10g> SELECT D.MONITORING FROM DBA_TABLES D WHERE D.TABLE_NAME='TEST_MON_MODS2';
MON
---
YES
示例如下：
SYS@orclasm > CREATE TABLE T_MON_20170602_LHR AS SELECT * FROM DBA_OBJECTS;
Table created.
SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';
no rows selected
SYS@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_MON_20170602_LHR');
PL/SQL procedure successfully completed.
SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';
no rows selected
SYS@orclasm > DELETE FROM T_MON_20170602_LHR WHERE ROWNUM <=10000;
10000 rows deleted.
SYS@orclasm > COMMIT;
Commit complete.
SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';
no rows selected
SYS@orclasm > EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';
TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP
------------------------------ ---------- ---------- ---------- -------------------
T_MON_20170602_LHR 0 0 10000 2017-06-02 19:26:03
& 说明：
有关SYS.MON_MODS$、SYS.MON_MODS_ALL$和DBA_TAB_MODIFICATIONS的更多内容可以参考作者BLOG：http://blog.itpub.net/26736162/viewspace-2145157/
本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗