【DB筆試面試650】在Oracle中，如何查詢表的DML操作數據變化量？

• 2019 年 10 月 10 日
• 筆記

題目部分

在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程式設計師面試筆試寶典》，作者：小麥苗