监视未使用索引

索引可以加快查询速度，减少全表扫描的发生。但索引的引入同时也带来很多负面的影响，如索引也需要占用存储空间；插入和删除行的时候，同时也需要更新索引。因此索引的引入也是一把双刃剑，我们需要确保系统中的每一个索引都能得到有效的利用。从Oracle9i开始提供了一个有效监控索引使用的方法，让管理员很轻松就能够了解相关索引的使用情况。

下面通过具体例子了解索引监控的方法。

SQL> create table indtest (id number, name varchar2(20));

Table created.

SQL> insert into indtest values(1, 'John');

1 row created.

SQL> insert into indtest values(2, 'Kitty');

1 row created.

SQL> insert into indtest values(3, 'Mike');

1 row created.

SQL> insert into indtest values(4, 'Peter');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table indtest add(constraint pk_indtest primary key(id));

Table altered.

SQL> select index_name,table_name,monitoring,used from v$object_usage;

no rows selected

SQL> alter index pk_indtest monitoring usage;

Index altered.

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE
------------------------------ ------------------------------ --- ---
PK_INDTEST                     INDTEST                        YES NO

SQL> set autotrace on
SQL> select * from indtest where id=1;

        ID NAME
---------- --------------------
         1 John

Execution Plan
----------------------------------------------------------
Plan hash value: 1349805652

--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| INDTEST    |
|*  2 |   INDEX UNIQUE SCAN         | PK_INDTEST |
--------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
        249  recursive calls
          0  db block gets
         53  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE
------------------------------ ------------------------------ --- ---
PK_INDTEST                     INDTEST                        YES YES