1.使用monitor index来监控索引使用 监控单个索引使用情况：alter index monitoring usage;

关闭监控： alter index nomonitoring usage;

查看监控情况：select * from v$object_usage

监控用户所有的索引：

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' NOMONITORING USAGE;'

FROM dba_indexes

where  owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR', 'SYSMAN', 'DBSNMP');

执行上面产生的脚本。

2.在dba_hist_sql_plan中查看索引使用：

select p.object_name c1, p.operation c2, p.options c3,count(1) c4

from dba_hist_sql_plan p, dba_hist_sqlstat s

where p.object_owner <> 'SYS'

and p.operation like '%INDEX%'

and p.sql_id = s.sql_id

group by p.object_name,p.operation, p.options order by 1,2,3;

3.使用缓存SQL执行计划来查看不使用的索引

with in_plan_objects as

(select distinct object_name from v$sql_plan where object_owner = USER)

select table_name, index_name,

case when object_name is null then 'NO' else 'YES'

end as in_cached_plan

from user_indexes left outer join in_plan_objects on (index_name = object_name);

with in_plan_objects as

(select distinct object_name from v$sql_plan where object_owner = 'TRANSGD_SGWT')

select owner, table_name, index_name,

case when object_name is null then 'NO' else 'YES'

end as in_cached_plan

from dba_indexes left outer join in_plan_objects on (index_name = object_name)

where owner = 'USERXXXT' and table_name = 'TABLEXXXX'

order by 4;