1.使用monitor index来监控索引使用

监控单个索引使用情况：alter index <INDEX_NAME> monitoring usage;

关闭监控： alter index <INDEX_NAME> 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;

 

参考：http://t.askmaclean.com/thread-2494-1-1.html