ORACLE索引监控

相关操作的SQL

--对于单个索引的监控，可以使用下面的命令来完成
alter index <INDEX_NAME> monitoring usage;
--关闭索引监控
alter index <INDEX_NAME> nomonitoring usage;
--观察监控结果(查询v$object_usage视图)
select * from v$object_usage

---

操作流程建议

1. 开启监控
2. 运行系统一段时间，最好是做全面的系统功能测试
3. 查看监控结果
4. 结合情况考虑是否删除

比如:
    某个表一个索引（包含主键）都未监控到是否有使用，得确认前台操作是否有相关的操作。
    有些表有索引1（F1,F2），索引2（F1,F2,F3）。可能在不同的情景下都会使用到，实际索引1在一般情况下是可以考虑删除的。
……

---

相关查询SQL：USER查询当前用户

SELECT T1.TABLE_NAME,
       T1.INDEX_NAME,
       NVL2(T2.OWNER, 'P', '') AS IS_PK,
       INDEX_COLUMNS,
       T3.MONITORING,
       T3.USED
  FROM (SELECT TABLE_NAME,
               INDEX_NAME,
               LISTAGG(COLUMN_NAME, ',') WITHIN GROUP(ORDER BY COLUMN_POSITION) AS INDEX_COLUMNS
          FROM USER_IND_COLUMNS
         WHERE INDEX_NAME IN
               (SELECT INDEX_NAME FROM USER_INDEXES WHERE INDEX_TYPE <> 'LOB')
         GROUP BY TABLE_NAME, INDEX_NAME) T1
  LEFT JOIN USER_CONSTRAINTS T2
    ON T1.INDEX_NAME = T2.CONSTRAINT_NAME
   AND T2.CONSTRAINT_TYPE = 'P'
  LEFT JOIN V$OBJECT_USAGE T3
    ON T1.INDEX_NAME = T3.INDEX_NAME
 ORDER BY INDEX_OWNER, TABLE_NAME, IS_PK, INDEX_NAME

---

##相关查询SQL：DBA查询多用户 ##

 SELECT T1.INDEX_OWNER,
        T1.TABLE_NAME,
        T1.INDEX_NAME,
        NVL2(T2.OWNER, 'P', '') AS IS_PK,
        INDEX_COLUMNS,
        T3.MONITORING,
        T3.USED
   FROM (SELECT INDEX_OWNER,
                TABLE_NAME,
                INDEX_NAME,
                LISTAGG(COLUMN_NAME, ',') WITHIN GROUP(ORDER BY COLUMN_POSITION) AS INDEX_COLUMNS
           FROM DBA_IND_COLUMNS
          WHERE (INDEX_OWNER, INDEX_NAME) IN
                (SELECT OWNER, INDEX_NAME
                   FROM DBA_INDEXES
                  WHERE OWNER IN ('USER1', 'USER2')
                    AND INDEX_TYPE <> 'LOB')
          GROUP BY INDEX_OWNER, TABLE_NAME, INDEX_NAME) T1
   LEFT JOIN (SELECT OWNER, CONSTRAINT_NAME
                FROM DBA_CONSTRAINTS
               WHERE OWNER IN ('USER1', 'USER2')
                 AND CONSTRAINT_TYPE = 'P') T2
     ON T1.INDEX_OWNER = T2.OWNER
    AND T1.INDEX_NAME = T2.CONSTRAINT_NAME
   LEFT JOIN V$OBJECT_USAGE T3
     ON T1.INDEX_NAME = T3.INDEX_NAME
  ORDER BY INDEX_OWNER, TABLE_NAME, IS_PK, INDEX_NAME