Monitoração de uso dos índices

--criar essa view para poder ver todos os outros indices em monitoração
--Viewing All Indexes Being Monitored Under Another User's Schema [ID 160712.1]
--criar no sys
create or replace view V$ALL_OBJECT_USAGE
(OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING)
as
select u.name, io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
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#;

create public synonym V$ALL_OBJECT_USAGE for V$ALL_OBJECT_USAGE;


--gera comando de monitorar os 30 maiores indices
select 'alter index '||owner||'.'||segment_name||' monitoring usage;' from (select owner,segment_name from dba_segments where segment_type='INDEX' order by bytes desc) where rownum <30;


--ver indexes não utilizados apos a ativação da monitoração
select * from V$ALL_OBJECT_USAGE where used='NO';

Acompanhe por pelo menos 30 dias, pois vc terá passado por um ciclo completo do seu ambiente e se ainda tiver índices não utilizados, faça uma gmud para remové-los.