how to check if the oracle index is being used in the look up or not

The rules of slap-happy data modeling would dictate that we place database indexes on primary keys, foreign keys, and then everything else that we believe to be highly queried. Usually, this leaves a cluttered database with many indexes that, over time, become unused and create difficulties for query optimization and overall Oracle database engine performance that include:

• Waste space on disk
• Create maintenance nightmares
• Cause confusion for developers trying to write optimal SQL
• Cause CPU and I/O resource overhead during INSERTs, UPDATEs, and DELETEs
• Force the Oracle optimizer to choose an execution path that does not necessarily take into consideration the layout or nuances of your data

These difficulties would suggest that DBAs need to drop indexes that are not being used.  This is actually quite easy in Oracle using the ALTER INDEX MONITORING USAGE command to monitor index usage over time. I would like to caution that this command should not be misused. Instead I would suggest that you use this command in conjunction with a predefined workload and time period. That way you can not only understand that an index is actually being used but you can relate that usage to a specific processing schedule. It’s great to know that an index is being used but it is even better to know when and where it is being used. That way you can schedule maintenance if needed.

So what does database index monitoring look like? Initially it’s good to know what indexes in your schema are actually being monitored. Make sure that the user you are logged in as has access to the system view V$OBJECT_USAGE and then execute this simple query to see those indexes being used (USER_INDEXES).

SQL> SELECT v.index_name, v.table_name,
v.monitoring, v.used,
start_monitoring, end_monitoring
FROM v$object_usage v, user_indexes u
WHERE v.index_name = u.index_name;
no rows selected

If you suspect or want to verify the usage of an index, simply issue the following ALTER INDEX command to start monitoring that index:

SQL> ALTER INDEX pk_emp MONITORING USAGE;
Index altered.

SQL> ALTER INDEX ix_emp_sal MONITORING USAGE;
Index altered.

Now when you select from the V$OBJECT_USAGE view you will see that monitoring has started for the associated indexes:

SQL> SELECT v.index_name, v.table_name,
v.monitoring, v.used,
start_monitoring, end_monitoring
FROM v$object_usage v, user_indexes u
WHERE v.index_name = u.index_name;

INDEX_NAME   TABLE_NAME    MON    USE     START_MONITORING    END_MONITORING
PK_EMP             EMP                    YES     NO       04/28/2009 10:16:51
IX_EMP_SAL     EMP                     YES     NO       04/28/2009 10:17:01

SELECTing from the underlying table that a monitored index is created for will trigger the USED flag in the V$OBJECT_USAGE view:

SQL> select * from emp where empno = 7844;
EMPNO ENAME    JOB                MGR  HIREDATE    SAL  COMM DEPTNO
7844    TURNER  SALESMAN  7698  08-SEP-81    1500    0     30

SQL> SELECT v.index_name, v.table_name,
v.monitoring, v.used,
start_monitoring, end_monitoring
FROM v$object_usage v, user_indexes u
WHERE v.index_name = u.index_name;

INDEX_NAME   TABLE_NAME     MON      USE     START_MONITORING    END_MONITORING
PK_EMP             EMP                     YES       YES      04/28/2009 10:16:51
IX_EMP_SAL      EMP                    YES        NO       04/28/2009 10:17:01

To stop the monitoring process, simply issue the ALTER INDEX NOMONITORING USAGE command and the END_MONITORING column will be updated in the V$OBJECT_USAGE view.

SQL> ALTER INDEX ix_emp_sal NOMONITORING USAGE;

Index altered.

SQL> SELECT v.index_name, v.table_name, v.monitoring, v.used,
start_monitoring, end_monitoring
FROM v$object_usage v, user_indexes u
WHERE v.index_name = u.index_name;

INDEX_NAME   TABLE_NAME   MON    USE    START_MONITORING    END_MONITORING
PK_EMP            EMP                    NO       YES     04/28/2009 10:16:51        04/28/2009 11:21:53
IX_EMP_SAL     EMP                   NO        NO      04/28/2009 10:17:01        04/28/2009 11:22:15

And if you’re wondering how different activity in the Oracle database effects the index monitoring, here are a few I’ve noticed:

• Monitoring information is persistent across database shutdown and startup

If you monitor the index again, previous usage information in the V$OBJECT_USAGE view is wiped out. This means you might want to periodically save object usage information.

• INSERTs do not effect the usage of an index
• UPDATEs to a table will effect usage of an index if that index is used for a lookup such as:
  UPDATE emp set comm = 10 WHERE empno = 9999;
• DELETEs to a table will effect usage of an index if that index is used for a look up such as:
  UPDATE emp WHERE empno = 9999;
• REBUILDs of an index will cause the index to be reported as being used such as:
  ALTER INDEX emp_pk REBUILD
• DROPing an index will remove the index from the V$OBJECT_USAGE view

The decision to create an index is not always securely rooted in explain plan analysis. More often than not you will find that indexes are created in a more whimsical and off-the-cuff manner. As part of your cleanup mechanisms you might try monitoring database indexing to see if indexes are actually being used. (Note that Ignite for Oracle now has a Top Objects feature that shows the wait time accumulating with each index).  Just take caution to sample a large enough period of time as adequate periods might be an hour, day, week, business quarter, or yearly cycle. And don’t forget to monitor newly created table indexes to prove to you, or disprove to developers, that an index is actually needed or not. And by all means DROP those ones not needed.