Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command

Checked for relevance on 03-July-2007

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