概述

我们在维护业务系统时，可能会建立很多索引，那么这些索引的使用到底怎么样，是否有些索引一直都没有用到过，那么oracle 是如何监控索引的使用状况，是否可以清除它们？

监控索引

一般有两种方式：

1、直接监控索引的使用情况

（1）设置所要监控的索引：ALTER INDEX IDX_T_XX MONITORING USAGE;

（2）查看该索引有没有被使用：SELECT * FROM V$OBJECT_USAGE;

（3）关闭监控：ALTER INDEX IDX_T_XX NOMONITORING USAGE;

2、schema级别索引监控

如果我们想在系统中监控所有的索引，可以通过下面脚本实现监控数据库所有的索引。注意我们要排除一些系统表的索引、以及LOB indexes。原因有下面两个：

1：LOB indexes不能修改，否则会报ORA-22864错误（ORA-22864: cannot ALTER or DROP LOB indexes）。

2：ORA-00701: object necessary for warmstarting database cannot be altered

今天主要也是讲schema级别这块的索引监控，下面介绍下三个脚本。

开启索引监控脚本

直接执行脚本来开启索引监控，当然监控索引时长非常重要，太短的话有可能导致查询出来的数据有问题，一般建议监控一周后即可，OLAP系统则需要适当延长监控的时间。

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' MONITORING USAGE;' enable_monitor,
 'ALTER INDEX ' || owner || '.' || index_name ||
 ' NOMONITORING USAGE;' disable_monitor
 FROM dba_indexes
 WHERE INDEX_TYPE != 'LOB'
 and owner IN
 (SELECT username FROM dba_users WHERE account_status = 'OPEN')
 AND owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR',
 'SYSMAN', 'DBSNMP')
 AND owner not like '%SYS%';

监控一个月就大概可以知道那些是无用的索引了。

监控索引使用情况脚本

虽然v$object_usage表能记录索引监控和使用的状态，但它不能统计索引被使用的次数和频率，只记录了在开启索引监控的时间段索引是否被使用过，这一点要值的注意。

另外需要注意的2点：

① 10g在收集统计信息时会导致索引被监控、这并非SQL语句产生、而在11g则不会出现这种情况了

② 外键索引不会因为主表的DML操作而被监控到、不要因为该索引没用而将它给删了。

查询V$OBJECT_USAGE就可以知道数据库对索引的使用情况了。通过一段时间的监控，就可以确定哪些是无用的索引。

另外，为了避免使用V$OBJECT_USAGE只能查询到当前用户下索引的监控情况，可以使用如下语句查询数据库中所有被监控索引的使用情况：

SELECT U.NAME OWNER,
 IO.NAME INDEX_NAME,
 T.NAME TABLE_NAME,
 DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING,
 DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,
 OU.START_MONITORING START_MONITORING,
 OU.END_MONITORING END_MONITORING
 FROM SYS.USER$ U,
 SYS.OBJ$ IO,
 SYS.OBJ$ T,
 SYS.IND$ I,
 SYS.OBJECT_USAGE OU
 WHERE I.OBJ# = OU.OBJ#
 AND IO.OBJ# = OU.OBJ#
 AND T.OBJ# = I.BO#
 AND U.USER# = IO.OWNER#;

历史执行计划分析索引使用情况脚本

其实也可以从视图DBA_HIST_SQL_PLAN中获取到数据库中所有索引的扫描次数情况，然后根据扫描次数和开发人员沟通是否需要保留索引。

WITH TMP1 AS
(SELECT I.OWNER INDEX_OWNER,
I.TABLE_OWNER,
TABLE_NAME,
INDEX_NAME,
INDEX_TYPE,
(SELECT NB.CREATED
FROM DBA_OBJECTS NB
WHERE NB.OWNER = I.OWNER
AND NB.OBJECT_NAME = I.INDEX_NAME
AND NB.SUBOBJECT_NAME IS NULL
AND NB.OBJECT_TYPE = 'INDEX') CREATED,
(SUM(S.BYTES) / 1024 / 1024) INDEX_MB,
(SELECT COUNT(1)
FROM DBA_IND_COLUMNS DIC
WHERE DIC.INDEX_NAME = I.INDEX_NAME
AND DIC.TABLE_NAME = I.TABLE_NAME
AND DIC.INDEX_OWNER = I.OWNER) COUNT_INDEX_COLS
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE I.INDEX_NAME = S.SEGMENT_NAME
AND I.OWNER = S.OWNER
AND S.OWNER NOT LIKE '%SYS%'
GROUP BY I.OWNER, I.TABLE_OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
HAVING SUM(S.BYTES) > 1024 * 1024),
TMP2 AS
(SELECT INDEX_OWNER,
INDEX_NAME,
PLAN_OPERATION,
(SELECT MIN(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))
FROM DBA_HIST_SNAPSHOT NB
WHERE NB.SNAP_ID = V.MIN_SNAP_ID) MIN_DATE,
(SELECT MAX(TO_CHAR(NB.END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))
FROM DBA_HIST_SNAPSHOT NB
WHERE NB.SNAP_ID = V.MAX_SNAP_ID) MAX_DATE,
COUNTS
FROM (SELECT D.OBJECT_OWNER INDEX_OWNER,
D.OBJECT_NAME INDEX_NAME,
D.OPERATION || ' ' || D.OPTIONS PLAN_OPERATION,
MIN(H.SNAP_ID) MIN_SNAP_ID,
MAX(H.SNAP_ID) MAX_SNAP_ID,
COUNT(1) COUNTS
FROM DBA_HIST_SQL_PLAN D, DBA_HIST_SQLSTAT H
WHERE D.OPERATION LIKE '%INDEX%'
AND D.SQL_ID = H.SQL_ID
GROUP BY D.OBJECT_OWNER, D.OBJECT_NAME, D.OPERATION, D.OPTIONS) V)
SELECT A.TABLE_OWNER,
A.TABLE_NAME,
A.INDEX_OWNER,
A.INDEX_NAME,
A.CREATED,
A.INDEX_TYPE,
A.INDEX_MB,
A.COUNT_INDEX_COLS,
B.PLAN_OPERATION,
CASE
WHEN MIN_DATE IS NULL THEN
(SELECT MIN(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))
FROM DBA_HIST_SNAPSHOT NB)
ELSE
MIN_DATE
END AS MIN_DATE,
CASE
WHEN MAX_DATE IS NULL THEN
(SELECT MAX(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))
FROM DBA_HIST_SNAPSHOT NB)
ELSE
MAX_DATE
END AS MAX_DATE,
COUNTS
FROM TMP1 A
LEFT OUTER JOIN TMP2 B
ON (A.INDEX_OWNER = B.INDEX_OWNER AND A.INDEX_NAME = B.INDEX_NAME);

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~