Monitoring Index

- Monitoring an Index determine weather the particular index is used optimally or not, so that you can drop un-necessary indexes.
- Enable Monitoring By : ALTER INDEX MONITORING USAGE ;
- Disable Monitoring By : ALTER INDEX NOMINTORING USAGE;
- Database View to Monitor Index : V$OBJECT_USAGE

 
SQL> desc v$object_usage
 Name
 ----------------------------
 INDEX_NAME          /* Index name */
 TABLE_NAME          /* Table Name */
 MONITORING          /* Monitoring on YES/NO */
 USED                /* INDEX USED YES/NO */
 START_MONITORING    /* DATE AND TIME OF START MONITORING */
 END_MONITORING      /* DATE AND TIME OF END MONITORING */

Example:

(1) To Start Monitoring

  SQL> ALTER TABLE SCOTT.EMP MONITORING USAGE;

(2) Check Status

  SQL> select * from V$OBJECT_USAGE;
 
INDEX_NAME       TABLE_NAME      MON USED START_MONITORING    END_MONITORING
--------------- ---------------- --- --- ------------------- --------------
EMPIDX2           EMP            YES NO  04/17/2003 15:20:03

Note : USED “NO” indicate, index is not used

(3) Run you application for a sufficient time to use the table and associated indexes

(4) check status again

SQL> select * from V$OBJECT_USAGE;
 
INDEX_NAME       TABLE_NAME      MON USED START_MONITORING    END_MONITORING
--------------- ---------------- --- --- ------------------- --------------
EMPIDX2           EMP            YES YES  04/17/2003 15:20:03

Note: USED “YES” indicate your application did you the index.

Switch off monitoring
(5) ALTER TABLE SCOTT.EMP NOMONITORING USAGE;

(6) Check the status

SQL> select * from V$OBJECT_USAGE;
 
INDEX_NAME       TABLE_NAME      MON USE START_MONITORING    END_MONITORING
--------------- ---------------- --- --- ------------------- --------------
EMPIDX2           EMP            YES YES  04/17/2003 15:20:03 04/17/2003 35:30:03

Note : END_MONITORING is populated with the time when you run the NOMONITORING USAGE

Important Points:
- Value is reset for a specific index , every time you specify monitoring usage.
- LOB and IOT cannot be monitored