Monitor Oracle index usage

From Oracle 9i, You can monitor the usage of Oracle indexes by using the following methods:

Alter index <schema>. <index> monitoring usage;

After enabling monitoring for an index, you can check whether the index is used:

To disable INDEX usage monitoring, use the following SQL:

Alter index <schema>. <index> nomonitoring usage;

Note that index usage monitoring adds some system overhead.

Select index_name, monitoring, used, start_monitoring, end_monitoring

From v $ object_usage;

Index_name monitoring used start_monitoring end_monitoring

Bytes ----------------------------------------------------------------------------------------

Aa No Yes 06/04/2006 12:02:38 06/05/2006 13:47:39

Aa1 No Yes 06/04/2006 12:02:40 06/05/2006 13:47:39

Note that the V $ object_usage view only displays the indexes monitored by the current user. Therefore, you cannot log on to the database from other users, to view the monitored indexes of all users, use the following SQL statement:

Select U. Name owner, Io. Name index_name, T. Name table_name,

Decode (bitand (I. Flags, 65536), 0, 'No', 'yes') monitoring,

Decode (bitand (ou. Flags, 1), 0, 'No', 'yes') used,

Ou. start_monitoring,

Ou. end_monitoring

From SYS. User $ U, SYS. OBJ $ Io, SYS. OBJ $ T, SYS. Ind $ I, SYS. object_usage ou

Where I. OBJ # = ou. OBJ #

And Io. OBJ # = ou. OBJ #

And T. OBJ # = I. Bo #

And U. User # = Io. Owner #