Monitoring DML operations

A very useful feature introduced in Oracle DB 9i called MONITORING. This feature monitors DML operations - insert/update/delete (and truncate) on monitored tables. Table monitoring can be implemented on a table by executing the following statement: "alter table TABLE_NAME monitoring". Once monitor option is enable for a table, all DML operations on it are register in a view - sys.DBA_TAB_MODIFICATIONS (based on sys.mon_mods$ table). The DML counters from this view represent the number of DML operations since the last gather statistics. The overhead on performance is not to be considerable since this mechanism work on memory and flush changes to DBA_TAB_MODOFICATIONS view periodically. In 9i, tables should be implemented with monitoring by executing "alter table ... monitoring", but in 10g monitor is enabled by default. The advantage of this feature is when gathering statistics. Based on the dba_tab_modifications view we can gather statistics for only tables that have more than X% changes (compared to num_rows in user_tables) since last gather statistics. Actually, there is no reason for us to re-analyze tables that has no enough changes. Here is a simple example: SQL> SQL> create table test1 2 (A number 3 ,B varchar2(100)); Table created SQL> SQL> select monitoring from user_tables where table_name = 'TEST1'; MONITORING ---------- YES -- In 10g table automatically has monitor option enabled. -- For 9i you should execute the following: SQL> SQL> alter table test1 monitoring; Table altered SQL> SQL> select * from user_tab_modifications; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- ------------- SQL> SQL> insert into test1 values (1,'a'); 1 row inserted SQL> insert into test1 values (2,'b'); 1 row inserted SQL> insert into test1 values (3,'c'); 1 row inserted SQL> commit; Commit complete SQL> SQL> select * from user_tab_modifications; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- ------------- SQL> 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 TRUNCATED DROP_SEGMENTS ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- ------------- TEST1 3 0 0 7/29/2007 1 NO 0 SQL> In the next post I will explain how to integrate monitoring feature with Gather Schema Statistics concurrent in Oracle Apps 11i. The monitoring discovered as very efficient when gathering statistics in Oracle Apps database or any quite large database. For questions, remarks, clarifications or any issue please leave a comment. Regards :-)