当我们需要统计一个表被操作的次数时,有个很重要的视图:all_tab_modifications,它会提供一个表的被操作(insert,update,delete等)的统计信息,看下官方说明:
ALL_TAB_MODIFICATIONS
describes tables accessible to the current user that have been modified since the last time statistics were gathered on the tables.
Note:
These views are populated only for tables with the MONITORING
attribute. They are intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate these views immediately when the actual modifications occur. Run the
FLUSH_DATABASE_MONITORING_INFO
procedure in the
DBMS_STATS
PL/SQL package to populate these views with the latest information. The
ANALYZE_ANY
system privilege is required to run this procedure.
通过官方解释,我们可以看到,要使用这个功能,前提是表的MONITORING属性要开启(DBA_TABLES)中可以看到表的这个属性(为YES才行)。
其次这个视图不会实时刷新,除非你调用过程:dbms_stats.FLUSH_DATABASE_MONITORING_INFO();
看下实验过程:
1.首先看表A的MONITORING属性
SELECT t.MONITORING FROM dba_tables t WHERE t.TABLE_NAME='A';
MON
---
YES
2.插入记录
INSERT INTO a SELECT * FROM a;
3.查询统计信息
SELECT * FROM all_tab_modifications WHERE table_owner='ORACLE';
你会发现没有任何提供的显示信息,执行过程
BEGIN
dbms_stats.FLUSH_DATABASE_MONITORING_INFO();
END;
再进行表A操作信息的查询
SELECT * FROM all_tab_modifications WHERE table_owner='ORACLE';
这个时候就能看到刚刚进行的操作信息了
所以如果你想知道一个过程中对表进行了多少次操作,你应该在执行前flush一次,过程执行完毕后再flush一次,用两次统计的差值来进行分析