使用“alter index ××× monitoring usage;”语句监控索引使用与否(转载secooler)

随着时间的累积，在没有很好的规划的情况下，中也许会存在大量长期不被使用的索引，如果快速的定位这些索引以便清理便摆在案头。我们可以使用“alter ××× ;”命令将索引至于状态下，经过一定的监控周期，那些不被使用到的索引便会在具体Schema下的v$object_usage视图中得以体现。展示一下这个过程，供参考。
友情提示：生产数据库中的索引添加和删除一定要慎重，需要做好充分的测试。

1.环境准备
1）创建表T
sec@ora10g> create table t (x int);

Table created.

2）初始化一条数据
sec@ora10g> insert into t values (1);

1 row created.

sec@ora10g> select * from t;

         X
----------
         1

3）在表T的X字段上创建索引
sec@ora10g> create index i_t on t(x);

Index created.

2.将索引I_T置于监控状态下
sec@ora10g> alter index I_T monitoring usage;

Index altered.

3.查看v$object_usage视图中记录的信息
sec@ora10g> col INDEX_NAME for a10
sec@ora10g> col TABLE_NAME a10
sec@ora10g> col START_MONITORING for a20
sec@ora10g> col END_MONITORING for a20
sec@ora10g> 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.模拟索引被使用
sec@ora10g> set autot on
sec@ora10g> 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视图中记录的信息
sec@ora10g> set autot off
sec@ora10g> 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状态变化
sec@ora10g> 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状态变化
sec@ora10g> 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”，表示再次启用监控后的这段时间内该索引没有被使用过。
停起对索引的监控的过程相当于索引监控重置的过程。

8.一次性生成当前用户下所有索引的监控
可以使用生成SQL脚本的方法来完成。
以对用户下所有索引生成监控语句为例
sys@ora10g> select 'alter index '||owner||'.'||index_name||' monitoring usage;' as "Monitor Indices Script" from dba_indexes where owner in ('SECOOLER');

Monitor Indices Script
---------------------------------------------------------------
alter index SECOOLER.I_T monitoring usage;
…… 省略 ……

如果您对PL/SQL熟悉的话，可以更方便的完成批量将索引置为被监控状态。
sys@ora10g> conn secooler/secooler
secooler@ora10g> begin
  2  for rec in (select index_name from user_indexes)
  3    LOOP
  4        dbms_output.put_line(rec.index_name);
  5        EXECUTE IMMEDIATE 'alter index '||rec.index_name||' monitoring usage';
  6    end loop;
  7  end;
  8  /

I_T
…… 省略其他索引名字 ……

PL/SQL procedure successfully completed.

9.小结
一般生产数据库很少使用这种方法（前提是做好规划），多见于测试数据库。测试数据库中出于对各种索引组合的测试需求，可能创建众多的索引，使用这种方法可以比较便捷的确认那些不被用到的索引。