监控索引的使用情况_3.2.2. 如何监控索引的使用情况-优快云博客

本文介绍如何监控数据库中的索引使用情况，通过启用监控、检查索引使用状态以及停止监控来评估索引的必要性。这种方法有助于识别未使用的索引，从而节省空间并提高DML操作性能。

监控数据库的中索引的使用情况，不用的索引可以删除不仅可以节省空间，而且可以增加dml操作的性能

如下监控方法：

1. Set the index under MONITORING:
    SQL> alter index I_EMP monitoring usage;
    Index altered.
    SQL> select * from v$object_usage;
    INDEX_NAME      TABLE_NAME      MON USED START_MONITORING    END_MONITORING
    --------------- --------------- --- ---- ------------------- -------------------
    I_EMP           EMP             YES NO   03/14/2001 09:17:19

2. Check if the monitored indexes are used or not through the USED column in
    V$OBJECT_USAGE view:
    SQL> select sal from emp where ename='SMITH';
          SAL
    ----------
           800
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
       2    1     INDEX (RANGE SCAN) OF 'I_EMP' (NON-UNIQUE)
    The explain plan indicates the index is used.

    SQL> select * from v$object_usage;
    INDEX_NAME      TABLE_NAME      MON USED START_MONITORING    END_MONITORING
    --------------- --------------- --- ---- ------------------- -------------------
    I_EMP           EMP             YES YES 03/14/2001 09:17:19
    If the index was not used, the DBA could drop this unused index.

3. To stop monitoring an index, use the following SQL statement:
SQL> alter index i_emp nomonitoring usage;
Index altered.

   SQL> select * from v$object_usage;
   INDEX_NAME      TABLE_NAME      MON USED START_MONITORING    END_MONITORING
   --------------- --------------- --- ---- ------------------- -------------------
   I_EMP           EMP             NO YES 03/14/2001 09:17:19 03/14/2001 09:55:24

   As soon as you turn monitoring on again for the index, both columns
   MONITORING and USED are reset.
   SQL> alter index i_emp monitoring usage;
   Index altered.

   SQL> select * from v$object_usage;
   INDEX_NAME      TABLE_NAME      MON USED START_MONITORING    END_MONITORING
   --------------- --------------- --- ---- ------------------- -------------------
   I_EMP           EMP             YES NO   03/14/2001 09:57:27