很多软件开发过程中，没有注意合理规划索引，造成一个表上有N多个索引，为后续的维护和优化带来麻烦。因此有时候需要监控已有的索引是否在使用，oracle提供了监控索引是否使用的工具，很简单，简要介绍一下。

首先，我们如果是监控一个表上的所有索引，可以这样先生成监控的命令：

SQL> select 'alter index '||index_name||' monitoring usage;' from user_indexes where table_name=upper('mpaymentappl')
2  /

'ALTERINDEX'||INDEX_NAME||'MONITORINGUSAGE;'
------------------------------------------------------------
alter index IDX_MPAYMENTAPPL_BCODE monitoring usage;
alter index MPAYMENTAPPL_FLAGS monitoring usage;
alter index MPAYMENTAPPL_PAICODE monitoring usage;

然后执行这些脚本就开始监控了，监控信息可通过V$OBJECT_USAGE查看，通过used列可知道这个索引是否被使用：

SQL> select * from V$OBJECT_USAGE
2  /

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IDX_MPAYMENTAPPL_BCODE         MPAYMENTAPPL                   YES NO  03/24/2010 10:55:27
MPAYMENTAPPL_FLAGS             MPAYMENTAPPL                   YES NO  03/24/2010 10:55:27
MPAYMENTAPPL_PAICODE           MPAYMENTAPPL                   YES NO  03/24/2010 10:55:28

取消监控某个索引：

SQL> ALTER INDEX MPAYMENTAPPL_FLAGS NOMONITORING USAGE;

Index altered.

SQL> select * from V$OBJECT_USAGE
2  /

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IDX_MPAYMENTAPPL_BCODE         MPAYMENTAPPL                   YES NO  03/24/2010 10:55:27
MPAYMENTAPPL_FLAGS             MPAYMENTAPPL                   NO  NO  03/24/2010 10:55:27 03/24/2010 10:57:19
MPAYMENTAPPL_PAICODE           MPAYMENTAPPL                   YES NO  03/24/2010 10:55:28

那些持续关注一定时间没有使用的索引就可以删除了，以提高DML操作效率。

2010/03/24 11:03 - Oracle

Reader's Comments

1. asword | 2010/03/24 16:03

   这个方法貌似不是很准确，我发现有以下问题，第一，可能很早之前该索引有人使用过，数据字典就记录了该信息，然后会一直持续下去，第二，没有该索引被使用的频率，如果一天只用一次，很可能也是属于应该删除的索引。

2. banping | 2010/03/25 11:30

   还找不到统计索引使用频率的方法

3. gerrard | 2010/04/02 23:24

   删掉object_usage里的记录,
   就可解决第一个问题,第二个问题...坐等楼主答复~ 哈哈

4. banping | 2010/04/03 23:08

   第二个问题几乎无解,也和Kamus同学探讨过这个问题,x$bh中有索引的block，里面有tch值，也许可以作为参考,但是肯定不准.