如何获取 Oracle 数据库中发生的插入/更新次数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6700010/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I get the number of inserts/updates occuring in an Oracle database?
提问by Nohsib
How do I get the total number of inserts/updates that have occurred in an Oracle database over a period of time?
如何获取一段时间内 Oracle 数据库中发生的插入/更新总数？
回答by Justin Cave
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_LEVELis 'TYPICAL' and the top 100 if the STATISTICS_LEVELis 'ALL') via something like
假设您已将 AWR 配置为保留所有 SQL 语句的数据（默认值是仅保留前 30 条 CPU、经过时间等。如果STATISTICS_LEVEL是“典型”，则保留前 100 条如果STATISTICS_LEVEL是“全部”）就像是
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.
并假设 SQL 语句在快照捕获它们之前不会从缓存中老化，那么您可以使用 AWR 表来完成其中的一些操作。
You can gather the number of times that an INSERT statement was executed and the number of times that an UPDATE statement was executed
您可以收集执行 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;
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.
请注意，这些查询包括您的应用程序发出的语句和 Oracle 在后台发出的语句。如果您想过滤掉某些 SQL 语句，您可以添加其他条件。
Similarly, you could get the total number of distinct INSERT and UPDATE statements
同样，您可以获得不同的 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 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 MONITORINGis enabled for each table (it is by default in 11g and I believe it is by default in 10g), i.e.
但是，Oracle 不会跟踪在给定时间间隔内插入或更新的行数。因此，您将无法从 AWR 获取该信息。您可以获得的最接近的方法是尝试利用 Oracle 所做的监控来确定统计信息是否过时。假设MONITORING为每个表启用（默认情况下在 11g 中，我相信默认情况下在 10g 中），即
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_MODIFICATIONStable. 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.
Oracle 会定期将每个表的插入、更新和删除的大致行数刷新到SYS.DBA_TAB_MODIFICATIONS表中。但这只会显示活动，因为统计信息是在表上收集的，而不是特定时间间隔内的活动。但是，您可以尝试编写一个流程，定期将这些数据捕获到您自己的表中并进行报告。
If you instruct Oracle to flush the monitoring information from memory to disk (otherwise there is a lag of up to several hours)
如果指示Oracle将监控信息从内存刷新到磁盘（否则会有长达数小时的滞后）
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
