监控索引的使用情况

灰太狼 2022-02-03 16:39 262阅读 0赞

监控数据库的中索引的使用情况，不用的索引可以删除不仅可以节省空间，而且可以增加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