Monitoring Index in Oracle

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

Example:
Start Monitoring
(1) ALTER TABLE SCOTT.EMP MONITORING USAGE;
Check Status
(2) 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 used the table and associated indexes
check status again
(4) 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;
Check the status
(6) 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