11gR2 -Oracle Index usage monitoring

To start monitoring the index usage:

ALTER INDEX MONITORING USAGE;


To stop:

ALTER INDEX MONITORING USAGE;


To monitor the status if you can login as owner then use

select * from v$object_usage;

If viewing the stats as other/super user then use below:

select d.username, 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') INDEX_USED,
       ou.start_monitoring,
       ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou,
     dba_users d
where io.owner# = d.user_id
  AND d.username = 'CN'
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#;
 
Ex:-