1.查找未使用索引

/* Formatted on 2020/5/12 下午 03:32:39 (QP5 v5.163.1008.3004) */
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);

或

/*对所有索引添加MONITOR*/
BEGIN
   FOR r IN (SELECT index_name FROM user_indexes)
   LOOP
      EXECUTE IMMEDIATE 'ALTER INDEX ' || r.index_name || ' MONITORING USAGE';
   END LOOP;
END;

SELECT INDEX_NAME,
       TABLE_NAME,
       USED,
       START_MONITORING
  FROM V$OBJECT_USAGE
 WHERE MONITORING = 'YES';