DBMS_STATS包使用

有时候，想查看一下表中数据的增删改次数，可以使用视图USER_TAB_MODIFICATIONS，USER_TAB_MODIFICATIONS describes modifications to all tables owned by the current user that have been modified since the last time statistics were gathered on the tables。

 

Note:

This view is populated only for tables with the MONITORING attribute. It is intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DBMS_STATS PL/SQL package to populate this view with the latest information. The ANALYZE_ANY system privilege is required to run this procedure.

sql>begin
            DbMS_STATS.flush_database_monitoring_info();
       end;

sql>select * from USER_TAB_MODIFICATIONS;

 

 

 

 

 

在使用DBMS_STATS分析表的时候，我们经常要保存之前的统计信息，以防分析后导致系统性能低下然后进行快速恢复。
首先创建一个备份表，该表是用来保存之前的统计信息

 

BEGIN
  --创建统计信息备份表
  DBMS_STATS.CREATE_STAT_TABLE(OWNNAME => USER,
                               STATTAB => 'stat_carmot_develop');
  --导出统计信息到备份表
  DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME => USER,
                                 STATTAB => 'stat_carmot_develop',
                                 STATOWN => USER);
  --删除统计信息
  DBMS_STATS.DELETE_SCHEMA_STATS(USER);
  --导入统计信息
  DBMS_STATS.IMPORT_SCHEMA_STATS(USER, 'stat_carmot_develop');
  --收集统计信息
  DBMS_STATS.GATHER_SCHEMA_STATS(USER);
  --恢复统计信息
  DBMS_STATS.RESTORE_SCHEMA_STATS(OWNNAME         => USER,
                                  AS_OF_TIMESTAMP => SYSTIMESTAMP - 0.5,
                                  FORCE           => TRUE);
END;