Checked for relevance on 05-Feb-2009
***Checked for relevance on 25-Jul-2010***

PURPOSE
  This document explains how to identify unused indexes in order to remove them.

 
SCOPE & APPLICATION
  DBAs must be aware that unused indexes:
      * consume storage space
      * degrade performance by unnecessary overheads during DML operations

REFERENCES

  Note:144070.1 Identifying unused indexes in Oracle9i


How to Identify Unused Indexes in Order to Remove Them:
=======================================================
The examples below work if the user logged is the owner of the index.

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 ptimizer=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

返回页首