Index Monitoring

Check to see if a indexed is being used.

SQL> select index_name, monitoring, used
2 from v$object_usage
3 where index_name = 'TIM_OBJECTID_IDX';

no rows selected

SQL> alter index TIM_OBJECTID_IDX monitoring usage;

Index altered.

Now if we run a query and trace to see the index
is used by the optimizer.

SQL> select object_id, object_name from tim_objects
2 where object_id = 5638;

OBJECT_ID OBJECT_NAME
---------- -------------------
5638 SCHEDULER$_SCHEDULE


Execution Plan
------------------------------------
Plan hash value: 1954542169

--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TIM_OBJECTS |
|* 2 | INDEX RANGE SCAN | TIM_OBJECTID_IDX |
--------------------------------------------------------

Checking again, we see the index has been used.

SQL> select index_name, monitoring, used
2 from v$object_usage
3 where index_name = 'TIM_OBJECTID_IDX';

INDEX_NAME MON USE
------------------------------ --- ---
TIM_OBJECTID_IDX YES YES

SQL> alter index tim_objectid_idx nomonitoring usage;

Index altered.

SQL> select index_name, monitoring, used
2 from v$object_usage
3 where index_name = 'TIM_OBJECTID_IDX';

INDEX_NAME MON USE
------------------------------ --- ---
TIM_OBJECTID_IDX NO YES

We can also display the start and end monitoring dates

SQL> desc v$object_usage;
Name Null? Type
----------------------------- -------- ------------
INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)

******************************************
keywords: index usage monitoring
******************************************
rdbms version: 9i 10g 11g
******************************************