相关操作的SQL
--对于单个索引的监控,可以使用下面的命令来完成
alter index <INDEX_NAME> monitoring usage;
--关闭索引监控
alter index <INDEX_NAME> nomonitoring usage;
--观察监控结果(查询v$object_usage视图)
select * from v$object_usage
操作流程建议
- 开启监控
- 运行系统一段时间,最好是做全面的系统功能测试
- 查看监控结果
- 结合情况考虑是否删除
比如:
某个表一个索引(包含主键)都未监控到是否有使用,得确认前台操作是否有相关的操作。
有些表有索引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