Tuning Index Usage - Expert Indexing in Oracle Database 11g - page 146

Both the MONITORING and the USED columns take either of two possible valuses: YES and NO . You can
monitor all indexes that the database has used by querying the V$OBJECT_USAGE view.
Once you're done examining the index usage, you can turn the montoring off.
SQL> alter index employees_idx1 nomonitoring usage;
Index altered.
SQL>
While monitoring indexes in the way shown here works well for the most part, index monitoring
only tells you whether the optimizer has planned the use of an index. It doesn't tell you whether the
query execution has actually used the index! Therefore, be very cautious before you drop an index after a
cursory glance at the V$OBJECT_USAGE view after turning index monitoring on.
 Note The database refreshes the V$OBJECT_USAGE view each time you turn monitoring on for a specific index.
Each time you turn on index monitoring for a specific index, the database removes the current usage information
and records a new start time for the monitoring.
You can also use the DBA_HIST_SQL_PLAN and the DBA_HIST_SQLSTAT views to find out the number of
times the database has accessed an index, as well as the type of index access, such as an index range scan
or a unique index scan. Here's an example:
SQL> select
2 d.object_name,
3 d.operation,
4 d.options,
5 count(1)
6 from
7 dba_hist_sql_plan d,
8 dba_hist_sqlstat h
9 where
10 d.object_owner <> 'SYS'
11 and
12 d.operation like '%INDEX%'
13 and
14 d.sql_id = h.sql_id
15 group by
16 d.object_name,
17 d.operation,
18 d.options
19 order by
20* 1,2,3;