oracle - 如何获取 Oracle 数据库中发生的插入/更新次数？

Question

如何获取一段时间内 Oracle 数据库中发生的插入/更新总数？

Answer 1

假设您已将 AWR 配置为保留所有 SQL 语句的数据(默认情况下，如果 STATISTICS_LEVEL 为“TYPICAL”且仅保留按 CPU、运行时间等排列的前 30 条数据)如果 STATISTICS_LEVEL 为“ALL”，则前 100 名)通过类似

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

并且假设 SQL 语句在快照捕获它们之前不会从缓存中老化，您可以使用 AWR 表来实现其中的一些目的。

您可以收集 INSERT 语句的执行次数和 UPDATE 语句的执行次数

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;

请注意，这些查询包括您的应用程序发出的语句和 Oracle 在后台发出的语句。如果您想过滤掉某些 SQL 语句，您可以添加其他条件。

同样，您可以获得不同的 INSERT 和 UPDATE 语句的总数

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 不会跟踪给定时间间隔内插入或更新的行数。因此您将无法从 AWR 获取该信息。最接近的方法是尝试利用 Oracle 的监控来确定统计信息是否过时。假设每个表都启用了MONITORING(在11g中是默认的，我相信在10g中是默认的)，即

ALTER TABLE table_name
  MONITORING;

Oracle 会定期将每个表插入、更新和删除的大概行数刷新到 SYS.DBA_TAB_MODIFICATIONS 表中。但这只会显示自从在表上收集统计数据以来的事件，而不是特定时间间隔内的事件。但是，您可以尝试编写一个过程来定期捕获这些数据到您自己的表中并报告该数据。

如果指示Oracle将监控信息从内存刷新到磁盘(否则会有长达几个小时的滞后)

BEGIN
  dbms_stats.flush_database_monitoring_info;
END;

您可以获得自上次收集统计信息以来每个表中已更改的行数的大致计数

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