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

方法一：在索引上应用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博客 ” ，链接：http://blog.itpub.net/21251711/viewspace-1991286/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21251711/viewspace-1991286/