Index Monitoring in oracle

Before oracle9i, monitoring the index is difficult one. For monitoring index, we need to run the explain plan and find the estimated execution plan to see if the index is used for the query. For instances, if we have multiple queries running in the application, then we have to run the explain plan for each query to find out the index usage.

Oracle9i has overcome this problem and easily identify the unused index. This feature helps us to remove unused index and reduce the database overhead.

Enable index

ALTER INDEX indexname MONITORING USAGE;

Disable index

ALTER INDEX indexname NOMINTORING USAGE;

Note: v$object_usage is gathering index monitoring information.

Here is the table structure for v$object_usage.

SQL> desc v$object_usage;
Name Type
----------------------- -------- ------------------------------
INDEX_NAME VARCHAR2(30) /* Name of the index*/
TABLE_NAME VARCHAR2(30) /* Name of the table*/
MONITORING VARCHAR2(3) /* Monitoring Turned on(YES/NO)*/
USED VARCHAR2(3) /* Index used (YES/NO) */
START_MONITORING VARCHAR2(19) /* Index monitoring start date*/
END_MONITORING VARCHAR2(19) /* Index monitoring end date. It will be null if the index is currently monitoring. It will be populated when we stop monitoring index*/
SQL>

Example for Index Monitoring

Step 1

SQL> show user
USER is "SCOTT"
SQL> select index_name from user_indexes where table_name='EMPLOYEE';

INDEX_NAME
------------------------------
IDX

SQL> alter index idx monitoring usage;

Index altered.


Step 2

Check the inforamation in v$object_usage.
SQL> ;
1 select monitoring,used,start_monitoring,end_monitoring from v$object_usage
2* where table_name='EMPLOYEE'
SQL> /

MON USE START_MONITORING END_MONITORING
--- --- ------------------- -------------------
YES NO 06/20/2008 10:59:33

SQL>

Step 3

Make use of index and check the v$object_usage.

SQL> select count(ename) from employee where empno=7902;

COUNT(ENAME)
------------
1

SQL> select monitoring,used,start_monitoring,end_monitoring from v$object_usage
2 where table_name='EMPLOYEE';

MON USE START_MONITORING END_MONITORING
--- --- ------------------- -------------------
YES YES 06/20/2008 10:59:33

SQL>

Step 4

The index is used and USED column in v$object_usage is updated YES. Now we disable the index.

SQL> alter index idx nomonitoring usage;

Index altered.
SQL> select monitoring,used,start_monitoring,end_monitoring from v$object_usage
2 where table_name='EMPLOYEE';

MON USE START_MONITORING END_MONITORING
--- --- ------------------- -------------------
NO YES 06/20/2008 10:59:33 06/20/2008 11:20:08

SQL>


Important Note:

Before removing unused index, Index monitoring should run for few months, batch process, year beginning reports, year end reports and make sure the index is not used anywhere. Then we can come to conclusion that the index is not used.

LOB & IOT cannot be monitored.