作者 | JiekeXu
来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）
如需转载请联系授权
大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来学习 Oracle 索引监控
一、索引介绍
索引是一种可以选择创建的数据库对象，它主要用于提高查询性能。数据库索引的用途和一本书前面的目录索引类似。书中的索引把书的主题和页码进行关联，想在一本书中查找信息时，首先查看目录索引，从中找到要查看的章节主题，确定相关的页码，通常比直接翻书查找要快的多。有了索引提供的信息，就可以直接翻到这本书中的具体页码。如果某个主题只在书的几页内出现，那么读取的页面数量是很少的。采用这种方式，一个主题在书中出现的次数越多，索引对他产生的作用就越小。
与书本的目录索引类似，数据库索引把用户感兴趣的列值连同其行标识符（ROWID）存储在一起。ROWID 包含了存储列值的表行在磁盘上的物理位置。有了 ROWID，Oracle 可以通过最少量的磁盘读取，有效地检索表中的数据，。采用这种方式，索引的功能就像表中数据的快捷方式，如果没有可用的索引，那么 Oracle 就必须读取表中的每一行，才能确定该行是否包含所需的信息。
（图片 引自《ORACLE DATABASE 11G 性能优化攻略》）
创建 Oracle 的索引时需要考虑很多的因素，Oracle 提供了多种索引属性和选项，这些对象都需要 DBA 或者开发手工创建，如果你选择了错误的索引类型或者没有正确使用某个属性，将会对性能产生不利的影响。下面所列的这些方面是创建索引之前必须考虑的：
- 索引的类型；
- 包含需要的列；
- 应该使用单独的列还是组合列；
- 特殊的属性，例如并行、关闭日志、压缩、不可见索引等；
- 唯一性；
- 命名规范；
- 表空间布局；
- 初始化空间需求及增长；
- 对 SELECT 语句性能的影响（提高）；
- 对 INSERT、UPDATE、DELETE 语句性能的影响；
- 如果基础表是分区表，那么使用全局索引还是本地索引。
Oracle 数据库索引类型及用途大概如下：
Oracle 数据库表和 B 树索引物理布局大概如下：
（图片 引自《ORACLE DATABASE 11G 性能优化攻略》）
Oracle 索引创建和维护说明如下：
二、Oracle 索引监控
合理的为数据库表上创建战略性索引，可以极大程度的提高了查询性能。但事实上日常中我们所创建的索引并非战略性索引，恰恰是大量冗余或是根本没有用到的索引耗用了大量的存储空间，导致 DML 性能低下。Oracle 提供了索引监控特性来初略判断未使用到的索引。本小节描述如何使用 Oracle 索引的监控。
冗余索引的弊端：
大量冗余和无用的索引导致整个数据库性能低下，耗用了大量的 CPU 与 I/O 开销，具体表现如下: a、耗用大量的存储空间(索引段的维护与管理) b、增加了DML完成的时间 c、耗用大量统计信息(索引)收集的时间 d、结构性验证时间 f、增加了恢复所需的时间
代码语言：javascript复制--查看表空间内的大表
col TABLE_NAME for a30
set pagesize 200
set linesize 200
col OWNER for a30
set linesize 200
select * from (select TABLESPACE_NAME,OWNER,SEGMENT_NAME "TABLE_NAME",to_number(decode(substr(BYTES/1024/1024,1,1),'.','0'||BYTES/1024/1024,BYTES/1024/1024)) total_MB
from dba_segments where TABLESPACE_NAME ='&tablespacename' and SEGMENT_TYPE='TABLE' order by total_MB desc ) where rownum<=10;
--如下 SQL 查看 CC 用户下大表 T_MATCH 都有哪些索引
set line 345 pages 345
COL INDEX_OWNER FOR A20
COL TABLE_OWNER FOR A20
COL TABLE_NAME FOR A25
COL INDEX_NAME FOR A30
COL COLUMN_NAME FOR A25
SELECT INDEX_OWNER,TABLE_OWNER,TABLE_NAME,INDEX_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='T_MATCH' and table_owner='CC' ORDER BY INDEX_NAME;索引监控的主要好处就是识别出不被使用的索引。这也就可以确定能够删除的索引，从而释放磁盘空间，并提高 DML 语句的性能。通常来说，在表上加一个索引会使该表上的 INSERT 操作的执行时间变成原来的一倍，再加一个索引就会再慢一倍，故创建合理的索引才是性能的关键。
很多时候，在项目测试阶段程序开发人员就已经涉及好了表结构以及索引，然后项目上线就不需要有过多的维护了，你会发现一张有十几个字段的表，竟然会有十个单列索引，甚至有些表的索引多达十七八个，看着就来气，询问开发人员只会告诉你由于历史原因或者为了一劳永逸……直接 EMO 了，创建了那么多的索引实际用到的估计也就三五个而已，那么怎么知道哪些索引被用到了呢？使用如下索引监控：
代码语言：javascript复制alter index CC.IDX_T_MATCH_ID monitoring usage;索引监控一般也不会只监控一个索引，一个表或者多个表甚至整个 SCHEMA 的索引都需要监控。如下使用 DBA 用户查到的 T_MATCH 表的所有索引都需要监控，则执行如下 SQL 的输出则可以监控整个表的索引使用情况。
代码语言：javascript复制select 'alter index '||owner|| '.'|| index_name || ' monitoring usage;' from dba_indexes
where table_name='T_MATCH' and owner='CC';
--如下使用普通用户监控多个表的索引使用情况
select 'alter index '|| index_name || ' monitoring usage;' from user_indexes
where table_name in ('T_LOAN','T_LOAN_DETAIL','T_OWNER','T_CNAPS_CODE','T_REPAY','T_REPAY_TEMP','T_BATCH');当监控一段时间后，比如几天，一周或者一个月，尽可能地让涉及到这个表的 SQL 在一定周期内有过执行，这样才能知道 SQL 是否真正使用到该索引，便可以查看 v$object_usage 视图了解情况。
代码语言：javascript复制select * from v$object_usage;
--普通用户查询
set line 456 pages 456
select * from v$object_usage where table_name like 'T_CRT%' order by USED,INDEX_NAME,TABLE_NAME;
set line 456
set pages 456
select * from v$object_usage order by TABLE_NAME,USED;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
I_T_COUNT_07 T_ACCOUNT YES NO 07/13/2022 18:36:30
I_T_COUNT_06 T_ACCOUNT YES NO 07/13/2022 18:36:09
P_T_ACCOUNT T_ACCOUNT YES NO 07/13/2022 18:34:25
I_T_COUNT_00 T_ACCOUNT YES NO 07/13/2022 18:34:23
IDX_ACCOUNT_TYPE_STATE T_ACCOUNT NO YES 07/13/2022 18:34:23 07/13/2022 18:52:35
U_T_COUNT_01 T_ACCOUNT NO YES 07/13/2022 18:34:40 07/13/2022 18:52:34
I_T_COUNT_01 T_ACCOUNT NO YES 07/13/2022 18:35:02 07/13/2022 18:52:35
I_T_COUNT_DETAIL_07 T_COUNT_DETAIL YES NO 07/06/2022 15:07:20
P_T_COUNT_DETAIL T_COUNT_DETAIL NO YES 07/06/2022 15:07:20 07/13/2022 18:52:36
I_T_COUNT_DETAIL_08 T_COUNT_DETAIL NO YES 07/06/2022 15:07:18 07/13/2022 18:52:36
I_T_COUNT_DETAIL_03 T_COUNT_DETAIL NO YES 07/06/2022 15:07:19 07/13/2022 18:52:35
I_T_COUNT_TRANSFER_00 T_COUNT_TRANSFER YES NO 06/29/2022 18:10:48
I_T_COUNT_TRANSFER_09 T_COUNT_TRANSFER NO YES 06/29/2022 18:10:19 06/29/2022 19:09:56
I_T_COUNT_TRANSFER_02 T_COUNT_TRANSFER NO YES 06/29/2022 18:10:17 06/29/2022 19:09:56
I_T_COUNT_TRANSFER_TEMP_05 T_COUNT_TRANSFER_TEMP YES NO 07/06/2022 15:05:16
I_T_COUNT_TRANSFER_TEMP_18 T_COUNT_TRANSFER_TEMP YES NO 07/06/2022 15:05:17
P_T_COUNT_TRANSFER_TEMP T_COUNT_TRANSFER_TEMP NO YES 07/06/2022 15:05:18 07/13/2022 18:52:36
I_T_AUTH_LOGIN_00 T_AUTH_LOGIN YES NO 02/15/2022 14:14:30
I_T_AUTH_LOGIN_08 T_AUTH_LOGIN YES NO 02/15/2022 14:14:30
I_T_AUTH_LOGIN_02 T_AUTH_LOGIN NO YES 02/15/2022 14:14:31 02/28/2022 14:39:02
I_T_AUTH_LOGIN_04 T_AUTH_LOGIN NO YES 02/15/2022 14:14:31 06/29/2022 19:09:57
I_T_AUTH_LOGIN_06 T_AUTH_LOGIN NO YES 02/15/2022 14:15:14 06/29/2022 19:09:57
I_T_AUTH_LOGIN_09 T_AUTH_LOGIN NO YES 02/28/2022 14:37:36 06/29/2022 19:09:57
I_T_CRT_06 T_CRT YES NO 06/29/2022 18:29:56
I_T_CRT_29 T_CRT YES NO 06/29/2022 18:33:59
I_T_CRT_30 T_CRT YES NO 06/29/2022 18:29:06
I_T_T_CRT_32 T_CRT YES NO 06/29/2022 18:34:20
I_T_CRT_02 T_CRT NO YES 06/29/2022 18:31:31 06/29/2022 19:11:23
I_T_CRT_33 T_CRT NO YES 06/29/2022 18:29:51 07/13/2022 18:52:37
I_T_T_CRT_31 T_CRT NO YES 06/29/2022 18:34:19 07/13/2022 18:52:37
I_T_CRT_TRADE_DETAIL_20 T_CRT_TRADE_DETAIL YES NO 06/29/2022 19:03:09
P_T_CRT_TRADE_DETAIL T_CRT_TRADE_DETAIL YES NO 06/29/2022 19:03:10
I_T_CRT_TRADE_DETAIL_19 T_CRT_TRADE_DETAIL YES NO 06/29/2022 19:03:09
I_T_CRT_TRADE_DETAIL_02 T_CRT_TRADE_DETAIL YES NO 06/29/2022 19:03:13
I_T_MATCH_REDEEM_TEMP_00 T_MATCH_REDEEM_TEMP YES NO 07/13/2022 18:38:37
I_T_MES_SMS_02 T_MES_SMS YES NO 06/29/2022 18:24:46 
注意数据库为 11g 时要使用业务用户查看，在 12c 以后，可以使用 DBA 用户查看 DBA 视图 dba_object_usage 或者 CDB 视图 CDB_OBJECT_USAGE。比 11g v$object_usage 视图多了一列 OWNER，其他列均一样。12c 及以上版本普通用户使用 USER_OBJECT_USAGE 视图查看。
vobject_usage 视图中只提供了当前连接用户的信息，可以查看 VOBJECT_USAGE 定义的 DBA_VIEWS 中的 TEXT 列来验证这一点：
代码语言：javascript复制select text from dba_views where view_name='V$OBJECT_USAGE';
--注意如下一行
where io.owner# = userenv('SCHEMAID')这一行命令该视图仅显示当前连接用户的信息，如果以 DBA 用户登录，要查看所有用户的索引监控情况，则可以执行如下 SQL 语句便可以查所有用户索引监控状态了。
代码语言：javascript复制--查看所有被监控索引的使用情况
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#;
OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ ------------------------------ --- --- ------------------- -------------------
SCOTT IDX_TEST_OBJID TEST YES NO 12/14/2021 14:44:35
PROD_CC U_T_MATCH_LOAN_01 T_MATCH_LOAN YES YES 08/01/2022 14:31:47
PROD_CC I_T_MATCH_LOAN_11 T_MATCH_LOAN YES NO 08/01/2022 14:31:50
--通过查看历史的执行计划，分析索引的使用情况。-- http://blog.itpub.net/26736162/viewspace-2120752/
--可以从视图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);如果监控一定周期后，需要取消该索引的监控，也很简单，只需要将添加监控的关键字“monitoring” 变为 “nomonitoring” 即可取消监控。取消索引监控后，视图 V$OBJECT_USAGE 的列 “END_MONITORING” 则会出现取消的时间，但值得注意的一点就是如果没有取消监控先删除了索引，此视图里记录的索引也将被清理。
alter index CC.I_T_FILE_06 nomonitoring usage;
--普通用户执行取消多个表索引监控
select 'alter index '|| index_name || ' nomonitoring usage;' from user_indexes where table_name in ('T_TRANSFER','T_DETAIL','T_TRANSFER_TEMP','T_YOUNT','T_REDEEM_TEMP','T_REDEEM','T_FREEZE');
--DBA 用户执行取消表索引监控
select 'alter index '||owner|| '.'|| index_name || ' nomonitoring usage;' from dba_indexes where table_name='T_REDEEM_TEMP' and owner='CC';当然取消索引监控，还可以通过视图中已经监控的索引取消掉，或者索引已经使用过的则可以取消掉。监控了一定周期后如果一直没有使用到，那么字段 “USED” 则一直是“NO”，那么这就可以删除掉了。
代码语言：javascript复制select 'alter index '|| index_name || ' nomonitoring usage;' from v$object_usage
where table_name in ('T_TRANSFER','T_DETAIL','T_TRANSFER_TEMP','T_AOUNT','T_REDEEM_TEMP','T_REDEEM','T_FREEZE') and MONITORING='YES' order by TABLE_NAME;
select 'alter index '|| index_name || ' nomonitoring usage;' from v$object_usage
where used='YES' and MONITORING= 'YES' and END_MONITORING is null order by TABLE_NAME;
drop index I_T_BATCH_00;
drop index I_T_BATCH_01;
drop index I_T_BATCH_02;
drop index I_T_BATCH_03;
drop index CC.I_T_BATCH_06;
drop index CC.I_T_BATCH_07;
drop index CC.I_T_BATCH_08;注意：添加索引监控一定要在业务低峰或者晚上停止业务的时候添加，不然会报“ORA-00054” 资源繁忙有业务占用导致失败。如果平时添加报错时可以再次执行一次，确保正确执行监控。
代码语言：javascript复制ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired全文完，希望可以帮到正在阅读的你
