Index Monitoring
Jump to navigation
Jump to search
Setting up
Enable monitoring:
select 'alter index ' || owner || '.' || index_name || ' monitoring usage;'
from dba_indexes
where owner = '?'
and index_type not in ('LOB');
Validate it has been enabled on all indexes (in case some were locked):
select index_name
from user_indexes
where index_type not in ('LOB')
minus
select index_name
from v$object_usage;
Verify, make sure to connect as the owner of the objects you are monitoring:
select *
from v$object_usage;
Querying
Total list
select *
from v$object_usage
where used = 'NO';
List of indexes with segment size (query won't work nicely for partitioned objects)
select u.index_name, u.table_name, round(s.bytes / 1048576) "MB", u.used
from v$object_usage u
, user_segments s
where u.used = 'NO'
and s.segment_name = u.index_name
order by s.bytes desc;
List of indexes without segments (count of this query + previous query should equal the total)
select u.index_name, i.segment_created
from v$object_usage u
, user_indexes i
where u.used = 'NO'
and u.index_name not in (select segment_name from user_segments)
and i.index_name = u.index_name;