Monitoring Table DML Activity in Oracle: Inserts, Updates, and Deletes

Monitoring Data Manipulation Language (DML) activity in Oracle provides insights into the volume of INSERT, UPDATE, and DELETE operations on a table. This information can be valuable for performance tuning and decision-making. Here, we walk through how to enable table monitoring, query DML statistics, and interpret the results.

How Monitoring Works

Oracle tracks the approximate number of DML operations (inserts, updates, deletes) on tables since the last statistics gathering operation. These statistics are temporarily stored in the System Global Area (SGA) and are periodically (approximately every three hours) written to the data dictionary by the SMON process. To retrieve up-to-date statistics, you can flush the monitoring information manually.

Steps to Enable and View Table Monitoring

1. Create a Test Table

Let’s start by creating a table and verifying its row count:

CREATE TABLE TEST AS SELECT OBJECT_NAME FROM ALL_OBJECTS;
-- Table TEST created.

SELECT COUNT(*) FROM TEST;

-- Output:
-- COUNT(*)
-- ----------
-- 120118

        

2. Check Monitoring Status

Query the DBA_TAB_MODIFICATIONS view to check DML statistics:

SELECT TABLE_NAME, INSERTS, UPDATES, DELETES, TRUNCATED, TIMESTAMP
FROM SYS.DBA_TAB_MODIFICATIONS
WHERE TABLE_OWNER = 'ATOORPU' AND TABLE_NAME = 'TEST';

-- Output:
-- No rows selected

        

3. Flush Monitoring Information

Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to flush any pending monitoring information:

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
-- Anonymous block completed

        

4. Enable Monitoring for the Table

Activate monitoring explicitly on the table (for pre-11g versions or demonstration purposes):

ALTER TABLE TEST MONITORING;

        

Query DBA_TAB_MODIFICATIONS again:

SELECT TABLE_NAME, INSERTS, UPDATES, DELETES, TRUNCATED, TIMESTAMP
FROM SYS.DBA_TAB_MODIFICATIONS
WHERE TABLE_OWNER = 'ATOORPU' AND TABLE_NAME = 'TEST';

-- Output:
-- No rows selected

        

5. Perform DML Operations

Insert, delete, or update rows in the table and recheck statistics:

DELETE FROM TEST WHERE ROWNUM < 1000;

SELECT COUNT(*) FROM TEST;

-- Output:
-- COUNT(*)
-- ----------
-- 119119

        

6. Flush Monitoring Information Again

Manually flush monitoring information to ensure statistics are updated:

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
-- Anonymous block completed

        

7. Retrieve Updated Statistics

Query DBA_TAB_MODIFICATIONS to see the recorded DML activity:

SELECT TABLE_NAME, INSERTS, UPDATES, DELETES, TRUNCATED, TIMESTAMP
FROM SYS.DBA_TAB_MODIFICATIONS
WHERE TABLE_OWNER = 'ATOORPU' AND TABLE_NAME = 'TEST';

-- Output:
-- TABLE_NAME       INSERTS    UPDATES    DELETES TRUNCATED TIMESTAMP
-- --------------- ---------- ---------- -------- --------- ---------
-- TEST                    0          0        999 NO        18-JUN-1

        

Important Notes

• Deprecation of MONITORING/NOMONITORING Keywords: Starting with Oracle Database 11g, the MONITORING and NOMONITORING keywords have been deprecated. Statistics are automatically collected, and these keywords are ignored. However, manual flushing of monitoring information may still be required in some scenarios.
• Manual Flushing: Use DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO to immediately update the DBA_TAB_MODIFICATIONS view with the latest DML statistics.
• Monitoring Use Cases: Monitoring is helpful for identifying tables with high DML activity, which can inform decisions on indexing, partitioning, or scheduling maintenance tasks like gathering statistics.

Conclusion

Monitoring DML activity in Oracle provides valuable insights into the operational workload of tables. While Oracle 11g and later versions handle statistics collection automatically, understanding and manually using monitoring features remains relevant for certain scenarios, especially for tuning and troubleshooting.