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 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 了，创建了那么多的索引实际用到的估计也就三五个而已，那么怎么知道哪些索引被用到了呢？使用如下索引监控：

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: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 视图查看。

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_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”，那么这就可以删除掉了。

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
————————————————————————————