Monitoring Table DML operations
                                                 Last update (2008-05-30 16:33:19)
                                                                                                                   Date added (2008-02-24 15:07:01)

Summary
Table monitoring can be implemented on a table by executing the following statement:
alter table TABLE_NAME monitoring;
This feature monitors DML operations - insert/update/delete (and truncate) on monitored tables. 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 Oracle9i, 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.
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search