How do I get the number of inserts/updates occuring in an Oracle database?

How do I get the total开发者_JAVA百科 number of inserts/updates that have occurred in an Oracle database over a period of time?

Assuming that you've configured AWR to retain data for all SQL statements (the default is to only retain the top 30 by CPU, elapsed time, etc. if the STATISTICS_LEVEL is 'TYPICAL' and the top 100 if the STATISTICS_LEVEL is 'ALL') via something like

BEGIN
  dbms_workload_repository.modify_snapshot_settings (
    topnsql => 'MAXIMUM'
  );
END;

and assuming that SQL statements don't age out of the cache before a snapshot captures them, you can use the AWR tables for some of this.

You can gather the number of times that an INSERT statement was executed and the number of times that an UPDATE statement was executed

SELECT sum( stat.executions_delta ) insert_executions
  FROM dba_hist_sqlstat stat 
       JOIN dba_hist_sqltext txt ON (stat.sql_id = txt.sql_id )
       JOIN dba_hist_snapshot snap ON (stat.snap_id = snap.snap_id)
 WHERE snap.begin_interval_time BETWEEN <<start time>> AND <<end time>>
   AND txt.command_type = 2;

SELECT sum( stat.executions_delta ) update_executions
  FROM dba_hist_sqlstat stat 
       JOIN dba_hist_sqltext txt ON (stat.sql_id = txt.sql_id )
       JOIN dba_hist_snapshot snap ON (stat.snap_id = snap.snap_id)
 WHERE snap.begin_interval_time BETWEEN <<start time>> AND <<end time>>
   AND txt.command_type = 6;

Note that these queries include both statements that your application issues and statements that Oracle issues in the background. You could add additional criteria if you want to filter out certain SQL statements.

Similarly, you could get the total number of distinct INSERT and UPDATE statements

SELECT count( distinct stat.sql_id ) distinct_insert_stmts
  FROM dba_hist_sqlstat stat 
       JOIN dba_hist_sqltext txt ON (stat.sql_id = txt.sql_id )
       JOIN dba_hist_snapshot snap ON (stat.snap_id = snap.snap_id)
 WHERE snap.begin_interval_time BETWEEN <<start time>> AND <<end time>>
   AND txt.command_type = 2;


SELECT count( distinct stat.sql_id ) distinct_update_stmts
  FROM dba_hist_sqlstat stat 
       JOIN dba_hist_sqltext txt ON (stat.sql_id = txt.sql_id )
       JOIN dba_hist_snapshot snap ON (stat.snap_id = snap.snap_id)
 WHERE snap.begin_interval_time BETWEEN <<start time>> AND <<end time>>
   AND txt.command_type = 6;

Oracle does not, however, track the number of rows that were inserted or updated in a given interval. So you won't be able to get that information from AWR. The closest you could get would be to try to leverage the monitoring Oracle does to determine if statistics are stale. Assuming MONITORING is enabled for each table (it is by default in 11g and I believe it is by default in 10g), i.e.

ALTER TABLE table_name
  MONITORING;

Oracle will periodically flush the approximate number of rows that are inserted, updated, and deleted for each table to the SYS.DBA_TAB_MODIFICATIONS table. But this will only show the activity since statistics were gathered on a table, not the activity in a particular interval. You could, however, try to write a process that periodically captured this data to your own table and report off that.

If you instruct Oracle to flush the monitoring information from memory to disk (otherwise there is a lag of up to several hours)

BEGIN
  dbms_stats.flush_database_monitoring_info;
END;

you can get an approximate count of the number of rows that have changed in each table since statistics were last gathered

SELECT table_owner,
       table_name,
       inserts,
       updates,
       deletes
  FROM sys.dba_tab_modifications