Oracle 索引监控
作者 | JiekeXu
来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）
如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)
大家好，我是 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、增加了恢复所需的时间
--查看表空间内的大表 col TABLE_NAME for a30set pagesize 200set linesize 200col OWNER for a30set linesize 200select * 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_MBfrom 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 345COL INDEX_OWNER FOR A20COL 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 了，创建了那么多的索引实际用到的估计也就三五个而已，那么怎么知道哪些索引被用到了呢？使用如下索引监控：
alter index CC.IDX_T_MATCH_ID monitoring usage;索引监控一般也不会只监控一个索引，一个表或者多个表甚至整个 SCHEMA 的索引都需要监控。如下使用 DBA 用户查到的 T_MATCH 表的所有索引都需要监控，则执行如下 SQL 的输出则可以监控整个表的索引使用情况。
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 视图了解情况。
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:35U_T_COUNT_01 T_ACCOUNT NO YES 07/13/2022 18:34:40 07/13/2022 18:52:34I_T_COUNT_01 T_ACCOUNT NO YES 07/13/2022 18:35:02 07/13/2022 18:52:35I_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:36I_T_COUNT_DETAIL_08 T_COUNT_DETAIL NO YES 07/06/2022 15:07:18 07/13/2022 18:52:36I_T_COUNT_DETAIL_03 T_COUNT_DETAIL NO YES 07/06/2022 15:07:19 07/13/2022 18:52:35I_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:56I_T_COUNT_TRANSFER_02 T_COUNT_TRANSFER NO YES 06/29/2022 18:10:17 06/29/2022 19:09:56I_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:36I_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:02I_T_AUTH_LOGIN_04 T_AUTH_LOGIN NO YES 02/15/2022 14:14:31 06/29/2022 19:09:57I_T_AUTH_LOGIN_06 T_AUTH_LOGIN NO YES 02/15/2022 14:15:14 06/29/2022 19:09:57I_T_AUTH_LOGIN_09 T_AUTH_LOGIN NO YES 02/28/2022 14:37:36 06/29/2022 19:09:57I_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:23I_T_CRT_33 T_CRT NO YES 06/29/2022 18:29:51 07/13/2022 18:52:37I_T_T_CRT_31 T_CRT NO YES 06/29/2022 18:34:19 07/13/2022 18:52:37I_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 视图查看。
v$object_usage 视图中只提供了当前连接用户的信息，可以查看 V$OBJECT_USAGE 定义的 DBA_VIEWS 中的 TEXT 列来验证这一点：
select text from dba_views where view_name='V$OBJECT_USAGE';
--注意如下一行where io.owner# = userenv('SCHEMAID')这一行命令该视图仅显示当前连接用户的信息，如果以 DBA 用户登录，要查看所有用户的索引监控情况，则可以执行如下 SQL 语句便可以查所有用户索引监控状态了。
--查看所有被监控索引的使用情况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_MONITORINGFROM SYS.USER$ U,SYS.OBJ$ IO,SYS.OBJ$ T,SYS.IND$ I,SYS.OBJECT_USAGE OUWHERE 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:35PROD_CC U_T_MATCH_LOAN_01 T_MATCH_LOAN YES YES 08/01/2022 14:31:47PROD_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.CREATEDFROM DBA_OBJECTS NBWHERE NB.OWNER = I.OWNERAND NB.OBJECT_NAME = I.INDEX_NAMEAND NB.SUBOBJECT_NAME IS NULLAND NB.OBJECT_TYPE = 'INDEX') CREATED,(SUM(S.BYTES) / 1024 / 1024) INDEX_MB,(SELECT COUNT(1)FROM DBA_IND_COLUMNS DICWHERE DIC.INDEX_NAME = I.INDEX_NAMEAND DIC.TABLE_NAME = I.TABLE_NAMEAND DIC.INDEX_OWNER = I.OWNER) COUNT_INDEX_COLSFROM DBA_SEGMENTS S, DBA_INDEXES IWHERE I.INDEX_NAME = S.SEGMENT_NAMEAND I.OWNER = S.OWNERAND S.OWNER NOT LIKE '%SYS%'GROUP BY I.OWNER, I.TABLE_OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPEHAVING 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 NBWHERE 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 NBWHERE NB.SNAP_ID = V.MAX_SNAP_ID) MAX_DATE,COUNTSFROM (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) COUNTSFROM DBA_HIST_SQL_PLAN D, DBA_HIST_SQLSTAT HWHERE D.OPERATION LIKE '%INDEX%'AND D.SQL_ID = H.SQL_IDGROUP 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,CASEWHEN MIN_DATE IS NULL THEN(SELECT MIN(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))FROM DBA_HIST_SNAPSHOT NB)ELSEMIN_DATEEND AS MIN_DATE,CASEWHEN MAX_DATE IS NULL THEN(SELECT MAX(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))FROM DBA_HIST_SNAPSHOT NB)ELSEMAX_DATEEND AS MAX_DATE,COUNTSFROM TMP1 ALEFT OUTER JOIN TMP2 BON (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”，那么这就可以删除掉了。
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” 资源繁忙有业务占用导致失败。如果平时添加报错时可以再次执行一次，确保正确执行监控。
ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~
❤️ 欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
————————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————