随着时间的累积，在没有很好的规划的情况下，数据库中也许会存在大量长期不被使用的索引，如果快速的定位这些索引以便清理便摆在案头。我们可以使用"alter index ××× monitoring usage;"命令将索引至于监控状态下，经过一定的监控周期，那些不被使用到的索引便会在具体Schema下的v$object_usage视图中得以体现。展示一下这个过程，供参考。
友情提示：生产数据库中的索引添加和删除一定要慎重，需要做好充分的测试。
1.环境准备
--1、创建表T
SQL> create table t (x int);
Table created.
--2、初始化一条数据
SQL> insert into t values (1);
1 row created.
SQL> select * from t;
? ? ? ? X
----------
? ? ? ? 1
--3、在表T的X字段上创建索引
SQL> create index i_t on t(x);
Index created.
?
?2.将索引I_T置于监控状态下
SQL> alter index I_T monitoring usage;
Index altered.
3.查看v$object_usage视图中记录的信息
?
SQL> col INDEX_NAME for a10
SQL> col TABLE_NAME a10
SQL> col START_MONITORING for a20
SQL> col END_MONITORING for a20
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORING? USED? ? ? START_MONITORING? ? END_MONITORING
---------- ---------- ---------- --------- -------------------- -----------------
I_T? ? ? ? T? ? ? ? ? YES? ? ? ? NO? ? ? ? 07/17/2010 22:27:13
?
?
此时MONITORING字段内容为“YES”，表示I_T已经处于被监控状态。USED字段内容为“NO”表示该索引还未被使用过。
4.模拟索引被使用
?
SQL> set autot on
SQL> select * from t where x = 1;
? ? ? ? X
----------
? ? ? ? 1
Execution Plan
----------------------------------------------------------
Plan hash value: 2616361825
-------------------------------------------------------------------------
| Id? | Operation? ? ? ? | Name | Rows? | Bytes | Cost (%CPU)| Time? ? |
-------------------------------------------------------------------------
|? 0 | SELECT STATEMENT |? ? ? |? ? 1 |? ? 13 |? ? 1? (0)| 00:00:01 |
|*? 1 |? INDEX RANGE SCAN| I_T? |? ? 1 |? ? 13 |? ? 1? (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
? 1 - access("X"=1)
Note
-----
? - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
? ? ? ? ? 0? recursive calls
? ? ? ? ? 0? db block gets
? ? ? ? ? 1? consistent gets
? ? ? ? ? 0? physical reads
? ? ? ? ? 0? redo size
? ? ? ? 508? bytes sent via SQL*Net to client
? ? ? ? 492? bytes received via SQL*Net from client
? ? ? ? ? 2? SQL*Net roundtrips to/from client
? ? ? ? ? 0? sorts (memory)
? ? ? ? ? 0? sorts (disk)
? ? ? ? ? 1? rows processed
?
从执行计划上可以看出，该查询使用到了索引I_T
5.再次查看v$object_usage视图中记录的信息
?
SQL> set autot off
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED? ? ? START_MONITORING? ? END_MONITORING
---------- ---------- --------- --------- -------------------- -----------------
I_T? ? ? ? T? ? ? ? ? YES? ? ? YES? ? ? 07/17/2010 22:27:13
?
此时USED字段内容变为“YES”，表示I_T索引在监控的这段时间内被使用过。
如果在一个较科学的监控周期下USED字段一直处于“NO”的状态，则可以考虑将此类索引删掉。
6.停止对索引的监控，观察v$object_usage状态变化
?
SQL> alter index I_T nomonitoring usage;
Index altered.
sec@ora10g> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED? ? ? START_MONITORING? ? END_MONITORING
---------- ---------- --------- --------- -------------------- -------------------
I_T? ? ? ? T? ? ? ? ? NO? ? ? ? YES? ? ? 07/17/2010 22:27:13? 07/17/2010 22:32:27
?
此时MONITORIN字段内容为“NO”，表示已经停止对索引I_T的监控。
7.再次启用索引监控，观察v$object_usage状态变化
?
SQL> alter index I_T monitoring usage;
Index altered.
sec@ora10g> select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORIN USED? ? ? START_MONITORING? ? END_MONITORING
---------- ---------- --------- --------- -------------------- ------------------
I_T? ? ? ? T? ? ? ? ? YES? ? ? NO? ? ? ? 07/17/2010 22:36:40
?
MONITORIN字段内容为“YES”，表示索引I_T处于被监控中；USED字段为“NO”，表示再次启用监控后