Monitoring DML Operations on the Tables

The Oracle feature Monitor, monitors the DML operations INSERT / UPDATE / DELETE and TRUNCATE on the 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 table_name 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.

Testing Process in as follows:-

Create the table with the name TEST, enable monitoring on that table then inserts some data.


Update data in the test table.


Delete Rows from the table.


Truncate Table.


Through the 'dbms_stats.flush_database_monitoring_info' package refress the database monitoring information.


Select the TABLE NAME / INSERTS / UPDATE / DELETE and TRUNCATED from sys.dba_tab_modifications view.

Summary:

1) Start control command: alter table test monitoring.
2) The actual number of operations on the table.
3) Through the package to refresh a database monitoring information, dbms_stats.flush_database_monitoring_info
4) Check the information on the operation table, sys.dba_tab_modifications 

Comments

Popular posts from this blog

How to create Oracle stored Procedures using TOAD for Oracle

Create Tables in Oracle Database using TOAD

How to Create Packages in Oracle Database using TOAD for Oracle