监控索引使用情况，主要用于判断该索引是否为无效索引，如果为无效索引最好是将该索引删除

官方文档

http://docs.oracle.com/cd/B19306_01/server.102/b14231/indexes.htm#sthref2560

首先，我们先建立测试表和索引

SYS@PROD>create table testdb.t as select * from dba_objects;

Table created.

SYS@PROD>conn testdb/testdb
Connected.
TESTDB@PROD>select count(*) from t;

  COUNT(*)
----------
     10161

TESTDB@PROD>create index ind_t_object_id on t(object_id);

Index created.

TESTDB@PROD>

开启索引监控

SYS@PROD>alter index testdb.ind_t_object_id monitoring usage;

Index altered.

检查测试结果

TESTDB@PROD>select object_id,object_name from t where object_id=300;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
       300 I_LOBFRAG_PARENTOBJFRAG$

TESTDB@PROD>col index_name for a20
TESTDB@PROD>col table_name for a10
TESTDB@PROD>col start_monitoring for a20
TESTDB@PROD>col end_monitoring for a20
TESTDB@PROD>select * from v$object_usage;

INDEX_NAME           TABLE_NAME MON USE START_MONITORING     END_MONITORING
-------------------- ---------- --- --- -------------------- --------------------
IND_T_OBJECT_ID      T          YES YES 01/28/2014 08:49:37

从结果我们可以看到，该索引是使用的并且索引监控是打开的，但是没有监控结束时间。下面我们来关闭

关闭索引监控

SYS@PROD>alter index testdb.ind_t_object_id nomonitoring usage;

Index altered.

再次检查监控结果

TESTDB@PROD>col index_name for a20
TESTDB@PROD>col table_name for a10
TESTDB@PROD>col start_monitoring for a20
TESTDB@PROD>col end_monitoring for a20
TESTDB@PROD>select * from v$object_usage;

INDEX_NAME           TABLE_NAME MON USE START_MONITORING     END_MONITORING
-------------------- ---------- --- --- -------------------- --------------------
IND_T_OBJECT_ID      T          NO  YES 01/28/2014 08:49:37  01/28/2014 08:53:47

从关闭后的结果可以看出，只要关闭了监控，监控时间就会显示出来。

索引使用情况监控，就是这些内容，比较简单。