索引監控-查詢從未被使用過的索引

方法一：在索引上應用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/，如需轉載，請註明出處，否則將追究法律責任。