方法一：在索引上应用monitoring usage子句，然后查询v$object_usage表，如下所示：
BEGIN
FOR r IN (SELECT index_name FROM user_indexes) LOOP
BEGIN
EXECUTE IMMEDIATE 'alter index ' || r.index_name ||
' monitoring usage';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
SELECT t.index_name, t.table_name, t.used, t.start_monitoring
FROM v$object_usage t
WHERE t.monitoring = 'YES';
注意：查询结果used为NO的表示从监控开始，该索引从未使用过。
方法二：通过检查视图v$sql_plan。这样可以避免对索引使用monitoring usage子句，但是一些使用过的索引的sql有可能被挤出缓存，查询语句如下所示：
WITH in_plan_objects AS
(SELECT DISTINCT t.object_name
FROM v$sql_plan t
WHERE t.object_owner = 'H2')
SELECT t1.table_name,
t1.index_name,
CASE
WHEN t2.object_name IS NULL THEN
'NO'
ELSE
'YES'
END AS in_cached_plan
FROM user_indexes t1
LEFT OUTER JOIN in_plan_objects t2
ON (t1.index_name = t2.object_name);
注意：查询结果in_cached_plan为NO的表示在缓存sql计划中找不到该索引。
没有被使任何查询使用的非键索引（非主键、非唯一键、非外键）或许可以考虑删除，使用monitoring usage或者查询v$sql_plan可以找出这些索引，方式如上面所提。

ITPub博客

索引监控-查询从未被使用过的索引

最新文章