Monitor Indexes

Which indexes get used in the database and which do not ?

Alter index index_name monitoring usage;

I’ve written a package that turns monitoring on for each index in turn and then turns it off again once it has been used. By this means you learn which indexes are used and which are not.

Latest on this is
http://www.oracloid.com/2006/05/vobject_usage-empty/

v$object_query only contains rows from the current user
so.........


Use this query

SELECT
aoj.*
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#
) aoj
,all_indexes u
WHERE aoj.index_name = u.index_name
and aoj.owner = u.owner