Index monitoring
After doing the usual alter index monitoring usage, you might wonder why you can not see anything in v$object_usage. This is because this view will only show usage by current user.
To be able to see a comprehensive list, use this query instead
select * from
(
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#
)
order by table_name;
Mind though, that this will simply tell you whether Oracle uses the index or not. If you are trying to rationalize the index usage within your database, this is not the answer of everything.
Given too many choices of indexes to use, Oracle could choose the wrong one. A used index is not a certain indication that it is a good index.