Oracle Monitoring Index Usage

Index is a associated structure with table, index is extra overhead statement with table, so if index is not utilized or unnessaccary index is created then drop that indexes.

Oracle provide a means of monitoring indexes to determine whether they are being used.

Let see in practical view.

SQL> drop table test purge;
Table dropped.

SQL> create table test as select * from all_objects;
Table created.

SQL> create index testidx on test(object_id);
Index created.

SQL> --now enable monitoring on index

SQL> alter index TESTIDX MONITORING usage;
Index altered.

SQL> ---execute query where index is created.

SQL> select count(*) from test where object_id=77204;
COUNT(*)

----------

1

SQL>---disable index mornitoring

SQL> alter index TESTIDX NOmonitoring usage;
Index altered.

SQL> ---now check the V$OBJECT_USAGE for index utilization. if we found nothing then drop index is better option

SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USED START_MONITORIN END_MONITORING

---------- ---------- --- ----- ---------------

---------------

TESTIDX TEST NO YES 07/31/2008 19:51:20

07/31/2008 19:52:04

NOTE: Used column is indicate "index" is utilize or not.