另类monitor index

                                           另类monitor index

     对index的很重要的一个方面就是看系统中的index是否被使用，以及使用的频率情况。oracle提供了index monitor usage来查看index 是否被使用，如下

SQL> create table t1 as select * from dba_objects;   --create test table t1

 

SQL> create index t1_indx on t1(object_id);   --create index on t1(object_id)
 
Index created.
 
SQL> alter index t1_indx monitoring usage;  ---monitor index usage

Index altered.

通过查询object_usage来查看是否被使用了.

SQL> desc object_usage;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                      NOT NULL NUMBER
 FLAGS                                     NOT NULL NUMBER
 START_MONITORING                                   VARCHAR2(19)
 END_MONITORING                                     VARCHAR2(19)

SQL> select object_id from t1 where object_id=10;   ----issue a simple query

 OBJECT_ID
----------
        10

再次查询object_usage，可以看到已经开使被使用了.

SQL>  select * from object_usage;

      OBJ#      FLAGS START_MONITORING    END_MONITORING
---------- ---------- ------------------- -------------------
     71122          1 07/31/2013 02:02:04

可以通过nomonitoring usage来关闭对index monitor

SQL> alter index t1_indx nomonitoring usage;  --- nomonitoring index usage

在生产环境上，会有很多人担心打开monitor index带来的性能影响，我们可以从v$sql_plan来查找索引的使用情况，而不需要打开monitor

SQL> select t1.object_name "index_name",t2.sql_text,t2.executions "executions"        --select index_name,sql_text,executions
  2   from v$sql_plan t1,v$sql t2                                                     ---join v$sql and v$sql_plan
  3   where t1.operation like '%INDEX%'
  4   and t1.plan_hash_value=t2.plan_hash_value
  5   and t1.object_name='T1_INDX';

通过对v$sql_plan,v$sql进行连接就可以找出当前哪些索引被使用了和使用的情况，而不需要打开monitoring usage,减少对生产环境的更改.