监控表及索引！

监控索引：

SQL> alter index TEST_PK monitoring usage;  --监控索引

索引已更改。

SQL> select * from v$object_usage;  --查看

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
TEST_PK                        TEST                           YES NO  11/23/2011 13:48:40

SQL> select count(*) from test where id = 2;

  COUNT(*)
----------
         1

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
TEST_PK                        TEST                           YES YES 11/23/2011 13:48:40

SQL> alter index TEST_PK nomonitoring usage;  --取消监控索引

索引已更改。

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
TEST_PK                        TEST                           NO  YES 11/23/2011 13:48:40 11/23/2011 13:49:04

注意：再次监控会清空v$object_usage里面的监控信息。还可以弄清楚数据库访问索引的次数以及访问索引的类型：

SQL> select d.object_name, d.operation, d.options, count(1)
  2    from dba_hist_sql_plan d, dba_hist_sqlstat h
  3   where d.object_name <> 'SYS'
  4     and d.operation like '%INDEX%'
  5     and d.sql_id = h.sql_id
  6     and d.object_name = 'PK_WEB_APP_BASE'
  7   group by d.object_name, d.operation, d.options
  8   order by 1, 2, 3;
 
OBJECT_NAME                     OPERATION                      OPTIONS                          COUNT(1)
------------------------------- ------------------------------ ------------------------------ ----------
PK_WEB_APP_BASE                 INDEX                          FULL SCAN                               1
PK_WEB_APP_BASE                 INDEX                          SAMPLE FAST FULL SCAN                   1
PK_WEB_APP_BASE                 INDEX                          UNIQUE SCAN                           302

v$object_usage视图是和登录的用户关联的，从这里只能看到本用户相关的索引的使用情况，如果通过sys用户进去，要看到索引使用情况可以使用以下脚本：

select u.name owner,
       io.name index_name,
       t.name table_name,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
       ou.start_monitoring start_monitoring,
       ou.end_monitoring end_monitoring
  from sys.user$        u,
       sys.obj$         io,
       sys.obj$         t,
       sys.ind$         i,
       sys.object_usage ou
 where i.obj# = ou.obj#
   and io.obj# = ou.obj#
   and t.obj# = i.bo#
   and u.user# = io.owner#;

监控表：

SQL> alter table test monitoring;  --监控表

表已更改。

SQL> delete from test where id = 123;

已删除 1 行。

SQL> delete from test where id = 456;

已删除 1 行。

SQL> delete from test where id = 789;

已删除 1 行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.flush_database_monitoring_info;  --刷新监控信息

PL/SQL 过程已成功完成。

SQL> select * from user_tab_modifications where table_name = 'TEST';

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP      TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- -------------- --- -------------
TEST                                                                                                  0          0          3 23-11月-11     NO              0

SQL> alter table test nomonitoring;  --取消监控

表已更改。

SQL> exec dbms_stats.gather_table_stats(user,'test',cascade=>true);

PL/SQL 过程已成功完成。

SQL>  select * from user_tab_modifications where table_name = 'TEST';

未选定行

注意：USER_TAB_MODIFICATIONS视图记录了当前用户中表自最后一次分析之后所有增、删、改等操作信息。

因为该视图中记录的信息会因表的分析自动清除，同时Oracle从性能上考虑并没有保证这个视图与表的操作实时保持一致，有一定的滞后，因此其中记录的信息仅可用于参考之目的。

dba_tab_modifications/all_tab_modifications/user_tab_modifications，这几个视图收集了表自从上一次分析之后的DML累积次数。但是要注意，考虑到性能的影响，Oracle并不是实时统计这个数据的，在Oracle9i之前，约3个小时SMON进程会刷新一次数据，而Oracle9i以后这个时间间隔变成了15分钟。

在早期Oracle版本中，若要启用这个功能需要使用“alter table t monitoring;”语句使表具有monitoring属性。到了Oracle10g时代，该功能已是默认行为，这主要归功于STATISTICS_LEVEL参数，该参数共有三种取值（STATISTICS_LEVEL = { ALL | TYPICAL | BASIC }），当其值是“TYPICAL”或“ALL”时该功能就会生效，因其默认值是“TYPICAL”所以该功能默认被启用。