鉴别不使用的索引

   有的索引根本用不着，但当在对表修改时，它也是会自动修改，这样会降低数据库的速度。下面来做个简单试验来监视索引是否使用。

首先创建个表，

SQL> create  table t (id int,sex char(1),name char(10));

Table created.

然后输入数据，

SQL> begin 
  2  for i in 1..100
  3  loop   
  4      insert into t values(i,'M','sun');
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

对表t创建一个索引，

SQL> create index t_idx1  on t(id);

Index created.

可以来查看一下，

SQL> select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
T_IDX1                         INDEX
T                              TABLE

在监控索引之前，先来介绍个动态性能视图,

V$OBJECT_USAGE

   V$OBJECT_USAGE displays statistics about index usage gathered from the database. You can use this view to monitor index usage. All indexes that have been used at least once can be monitored

and displayed in this view.

       V$OBJECT_USAGE显示有关于数据库中索引使用情况的统计。能够利用这个视图来监控索引使用。该视图能够监控和显示最近一次使用到的所有索引。

ColumnDatatypeDescriptionINDEX_NAMEVARCHAR2(30)Index name in sys.obj$.name(索引的名字)TABLE_NAMEaVARCHAR2(30)Table name in sys.obj$.name(索引的基表)MONITORINGVARCHAR2(3)YES|NO(是否监控，yes 是|no 否)USEDVARCHAR2(3)YES|NO(是否使用，yes 是|no 否)START_MONITORINGVARCHAR2(19)Start monitoring time insys.object_stats.start_monitoring(索引监控开始时间)END_MONITORINGVARCHAR2(19)End monitoring time insys.object_stats.end_monitoring(索引监控结束时间

 

打开并开始监控索引的使用，

SQL> alter index t_idx1 monitoring usage;

Index altered.

来检查一下是否开始监控，

SQL> select * from  v$object_usage;

INDEX_NAME                     TABLE_NAME      MON     USE     START_MONITORING         END_MONITORING
------------------------------ --------------------------   ------     -------    -----------------------------               -------------------
T_IDX1                                              T                   YES       NO      04/18/2013 00:23:06

在上面显示MON是yes表示开始监视，use为no表示没有使用，还用监视开始的时间和结束时间。

在这里做一下可以对索引的使用，在来看看动态性能视图的变化，

 

SQL> set  autot on exp
SQL> select * from  t  where id=88;

        ID S NAME
---------- - ----------
        88 M sun

Execution Plan
----------------------------------------------------------
Plan hash value: 4055207394

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    28 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     1 |    28 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("ID"=88)

从查看执行计划中可以看到使用了索引，

SQL> set autot off
SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME          MON  USE       START_MONITORING         END_MONITORING
------------------------------ ------------------------------   -------    ------      ------------------------------             -------------------
T_IDX1                                          T                     YES     YES        04/18/2013 00:23:06

可以看到USE改变成了YES，表明使用过了。

关闭监控索引，

SQL> alter index t_idx1 nomonitoring usage;

Index altered.

再来看一下动态性能视图，

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME         MON    USE     START_MONITORING     END_MONITORING
------------------------------ ------------------------------   --------   ------     -------------------------------      --------------------------
T_IDX1                                         T                     NO       YES     04/18/2013 00:23:06          04/18/2013 00:41:38

这时候可以看出，MON改变成了NO，还标出了监控的时间段。