数据库运行的时间长了，里面会有很多索引，索引多了，不仅占用存储空间，也可能影响到关键业务语句的性能。这时，就要考虑删除多余的索引了。在没有业务人员参与的情况下，删除一条索引，需要先确认这条索引是否在查询时用到。这篇文章介绍一下Oracle数据库中怎样监控索引使用的方法。
1 索引监控（Oracle 12.2以前版本）
1.1 使用索引监控
索引监控针对每一个索引单独设置，使用alter index 命令，在Oracle 12.1以前版本的数据库，查询v$object_usage视图，这个视图没有owner列，必须以对象的所有者登陆才能看到对象的使用信息。从Oracle 12.1开始，可以使用{DBA|USER]}_OBJECT_USAGE视图来查询，这个视图有owner列。
ALTER INDEX hr.EMP_NAME_IX MONITORING USAGE;--打开索引监控
SELECT index_name,table_name,monitoring,used,start_monitoring,end_monitoring
FROM dba_object_usage;--查询视图
INDEX_NAME |TABLE_NAME|MONITORING|USED|START_MONITORING |END_MONITORING|
-----------+----------+----------+----+-------------------+--------------+
EMP_NAME_IX|EMPLOYEES |YES |NO |04/22/2025 01:57:25| |
--索引监控选项已经打开，索引为被使用
--运行下列SQL语句
SELECT * FROM EMPLOYEES WHERE FIRST_NAME='Steven';
--再次查询dba_object_usage视图
-----------+----------+----------+----+-------------------+--------------+
INDEX_NAME |TABLE_NAME|MONITORING|USED|START_MONITORING |END_MONITORING|
-----------+----------+----------+----+-------------------+--------------+
EMP_NAME_IX|EMPLOYEES |YES |YES |04/22/2025 01:57:25| |
-----------+----------+----------+----+-------------------+--------------+
显示索引已被使用，使用alter index命令关闭此索引的监控
ALTER INDEX hr.EMP_NAME_IX NOMONITORING USAGE;
--再次查询dba_object_usage视图
INDEX_NAME |TABLE_NAME|MONITORING|USED|START_MONITORING |END_MONITORING |
-----------+----------+----------+----+-------------------+-------------------+
EMP_NAME_IX|EMPLOYEES |NO |YES |04/22/2025 01:57:25|04/22/2025 02:13:34|
--可以看到索引EMP_NAME_IX监控开始和结束时间点。
从索引监控的结果来看，它只能告诉我们在监控时间段内索引是否使用过，对于使用的频率和时间点则一无所知。
1.2 外键索引
Oracle 官方概念手册上有一句话，“外键上总是应该建立索引，除非父主键或者唯一索引从不更新或者删除”。
外键有无索引会导致父主键DML（更新或者删除）操作时子表加锁的类型和范围不同。当一个外键没有索引时，参考主键上的DML操作（删除或者更新）会在子表上加共享行排他表锁（share-subexclusive table lock, SSX），这个锁用来阻止其它事务对子表的DML操作。如果DML操作影响到多行，每一行都要进行锁的获取和释放操作。当父表上的更新和删除操作压力较大时，会在子表上造成明显的锁竞争。当一个外键有索引时，父主键上的DML操作在子表上加行共享锁（subshare table lock, SS）。这个类型的锁阻止其它事务在子表上加整体表锁，不阻塞子表或者附表上的DML操作。只有和操作父主键相关的子表行会被锁定。
1.3 索引监控的限制
Oracle的索引监控可能会不太准确，在Oracle 12.2 版本以前，索引监控基于在SQL语句优化阶段执行计划选择的索引。在语句实现阶段，有些索引也可能被使用，比如有的索引用来检查表上的限制，这些索引的使用可能不会被记录到对象使用视图中。也就是说，它监控到的索引一定是系统使用过的，它没监控到的索引也可能被使用过，不能用它作为索引未使用的绝对判断条件。
从Oracle 12.2 版本开始，Oracle的索引监控功能底层有了变化，基于运行时的索引使用情况进行统计，漏掉数据库使用索引的情况降低了不少。
2 索引追踪
从Oracle 12.2 开始，索引使用追踪（Index Usage Tracking）替换了之前版本的索引监控功能。索引使用追踪默认是打开的，在缺省情况下，采用的追踪方法是采样，而不是追踪所有索引的使用。
2.1 采集设置
通过隐含参数_iut_stat_collection_type控制索引追踪的采集类型。这个参数有两个值all和sampled，参数值可以在会话级和实例级设置，如下，在会话级设置
ALTER SESSION SET "_iut_stat_collection_type"=ALL;
ALTER SESSION SET "_iut_stat_collection_type"=SAMPLED;
2.2 V$INDEX_USAGE_INFO视图
索引使用跟踪的统计信息存储在内存之中，它的顶级视图是V$INDEX_USAGE_INFO，视图的信息如下：
DESC v$index_usage_info
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_STATS_ENABLED NUMBER --索引追踪是否开启，默认是开启的
INDEX_STATS_COLLECTION_TYPE NUMBER --采集类型，1（默认值）是采样，0是追踪所有索引
ACTIVE_ELEM_COUNT NUMBER --上一次刷新以来的活跃索引
ALLOC_ELEM_COUNT NUMBER --可能是自数据库启动以来的活动索引的总的数量
MAX_ELEM_COUNT NUMBER
FLUSH_COUNT NUMBER
TOTAL_FLUSH_DURATION NUMBER
LAST_FLUSH_TIME TIMESTAMP(3) --统计信息上一次刷新到磁盘的时间
STATUS_MSG VARCHAR2(256)
CON_ID NUMBER
每隔15分钟，索引使用信息被刷新到磁盘，视图中的LAST_FLUSH_TIME被更新，ACTIVE_ELEM_COUNT计数被清零。下面是数据库刚启动时还未曾刷新时查询视图的输出
SELECT * FROM V$INDEX_USAGE_INFO;
-------------------+---------------------------+-----------------+----------------+--------------+-----------+--------------------+-----------------------+----------+------+
INDEX_STATS_ENABLED|INDEX_STATS_COLLECTION_TYPE|ACTIVE_ELEM_COUNT|ALLOC_ELEM_COUNT|MAX_ELEM_COUNT|FLUSH_COUNT|TOTAL_FLUSH_DURATION|LAST_FLUSH_TIME |STATUS_MSG|CON_ID|
-------------------+---------------------------+-----------------+----------------+--------------+-----------+--------------------+-----------------------+----------+------+
1| 0| 7| 7| 30000| 1| 1|2025-04-23 01:36:40.293| | 3|
-------------------+---------------------------+-----------------+----------------+--------------+-----------+--------------------+-----------------------+----------+------+
上一次刷新时间是数据库启动的时间，15分钟之后再查询这个视图
-------------------+---------------------------+-----------------+----------------+--------------+-----------+--------------------+-----------------------+----------+------+
INDEX_STATS_ENABLED|INDEX_STATS_COLLECTION_TYPE|ACTIVE_ELEM_COUNT|ALLOC_ELEM_COUNT|MAX_ELEM_COUNT|FLUSH_COUNT|TOTAL_FLUSH_DURATION|LAST_FLUSH_TIME |STATUS_MSG|CON_ID|
-------------------+---------------------------+-----------------+----------------+--------------+-----------+--------------------+-----------------------+----------+------+
1| 0| 3| 7| 30000| 3| 902077|2025-04-23 02:06:14.998| | 3|
-------------------+---------------------------+-----------------+----------------+--------------+-----------+--------------------+-----------------------+----------+------+
ALLOC_ELEM_COUN（已分配的条目）的值和上面一样，仍然是7，活跃索引数量被清零后重新计算，值为3，刷新时间也已更新。
2.3 DBA_INDEX_USAGE
索引使用信息被刷新到磁盘后，对象级的使用信息可以在DBA_INDEX_USAGE中看到，视图定义如下：
DESC dba_index_usage
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
OWNER NOT NULL VARCHAR2(128)
TOTAL_ACCESS_COUNT NUMBER --索引被访问的总次数
TOTAL_EXEC_COUNT NUMBER --索引参与执行的次数
TOTAL_ROWS_RETURNED NUMBER
BUCKET_0_ACCESS_COUNT NUMBER --索引为被访问
BUCKET_1_ACCESS_COUNT NUMBER --索引被访问一次
BUCKET_2_10_ACCESS_COUNT NUMBER --索引被访问2-10次
BUCKET_2_10_ROWS_RETURNED NUMBER
BUCKET_11_100_ACCESS_COUNT NUMBER
BUCKET_11_100_ROWS_RETURNED NUMBER
BUCKET_101_1000_ACCESS_COUNT NUMBER
BUCKET_101_1000_ROWS_RETURNED NUMBER
BUCKET_1000_PLUS_ACCESS_COUNT NUMBER
BUCKET_1000_PLUS_ROWS_RETURNED NUMBER
LAST_USED DATE
这个视图里除了可以查询到一条索引使用的基本信息之外，也可以查询到索引使用的直方图信息（视图中以BUCKET_0_ACCESS_COUNT开头的列）。可以查询索引使用的基本信息，如下
SELECT NAME, OWNER,TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT,TOTAL_ROWS_RETURNED,LAST_USED FROM DBA_INDEX_USAGE WHERE owner='SH';
--------------------+-----+------------------+----------------+-------------------+-----------------------+
NAME |OWNER|TOTAL_ACCESS_COUNT|TOTAL_EXEC_COUNT|TOTAL_ROWS_RETURNED|LAST_USED |
--------------------+-----+------------------+----------------+-------------------+-----------------------+
TIMES_PK |SH | 5900144| 8| 5900144|2024-09-09 09:03:48.000|
CHANNELS_PK |SH | 7350762| 26| 7350834|2025-04-23 01:51:12.000|
COUNTRIES_PK |SH | 1366843| 3| 1366843|2025-04-23 01:51:12.000|
PRODUCTS_PK |SH | 5842079| 673| 5843286|2025-04-23 01:51:12.000|
CUSTOMERS_PK |SH | 3918668| 712| 4362660|2025-04-23 01:51:12.000|
SALES_CHANNEL_IDX |SH | 5408| 5408| 4969102944|2025-03-31 03:23:12.000|
IDX_SALE_N_C |SH | 6| 4| 0|2025-04-23 01:51:12.000|
IDX_COUNTRY_C |SH | 2| 2| 111000|2025-04-23 01:51:12.000|
PRODUCTS_PROD_CAT_IX|SH | 17| 17| 442|2024-12-11 07:10:54.000|
--------------------+-----+------------------+----------------+-------------------+-----------------------+
查询索引使用的直方图信息
SELECT NAME, OWNER,BUCKET_0_ACCESS_COUNT, BUCKET_1_ACCESS_COUNT,BUCKET_2_10_ACCESS_COUNT,BUCKET_11_100_ACCESS_COUNT
,BUCKET_101_1000_ACCESS_COUNT,BUCKET_1000_PLUS_ACCESS_COUNT
,BUCKET_2_10_ROWS_RETURNED,BUCKET_11_100_ROWS_RETURNED,BUCKET_101_1000_ROWS_RETURNED,BUCKET_1000_PLUS_ROWS_RETURNED
,LAST_USED
FROM DBA_INDEX_USAGE WHERE owner='SH';
--------------------+-----+---------------------+---------------------+------------------------+--------------------------+----------------------------+-----------------------------+-------------------------+---------------------------+-----------------------------+------------------------------+-----------------------+
NAME |OWNER|BUCKET_0_ACCESS_COUNT|BUCKET_1_ACCESS_COUNT|BUCKET_2_10_ACCESS_COUNT|BUCKET_11_100_ACCESS_COUNT|BUCKET_101_1000_ACCESS_COUNT|BUCKET_1000_PLUS_ACCESS_COUNT|BUCKET_2_10_ROWS_RETURNED|BUCKET_11_100_ROWS_RETURNED|BUCKET_101_1000_ROWS_RETURNED|BUCKET_1000_PLUS_ROWS_RETURNED|LAST_USED |
--------------------+-----+---------------------+---------------------+------------------------+--------------------------+----------------------------+-----------------------------+-------------------------+---------------------------+-----------------------------+------------------------------+-----------------------+
TIMES_PK |SH | 0| 5900144| 0| 0| 0| 0| 0| 0| 0| 0|2024-09-09 09:03:48.000|
CHANNELS_PK |SH | 0| 7350744| 18| 0| 0| 0| 90| 0| 0| 0|2025-04-23 01:51:12.000|
COUNTRIES_PK |SH | 0| 1366843| 0| 0| 0| 0| 0| 0| 0| 0|2025-04-23 01:51:12.000|
PRODUCTS_PK |SH | 0| 5842062| 0| 17| 0| 0| 0| 1224| 0| 0|2025-04-23 01:51:12.000|
CUSTOMERS_PK |SH | 0| 3918660| 0| 0| 0| 8| 0| 0| 0| 444000|2025-04-23 01:51:12.000|
SALES_CHANNEL_IDX |SH | 0| 0| 0| 0| 0| 5408| 0| 0| 0| 4969102944|2025-03-31 03:23:12.000|
IDX_SALE_N_C |SH | 6| 0| 0| 0| 0| 0| 0| 0| 0| 0|2025-04-23 01:51:12.000|
IDX_COUNTRY_C |SH | 0| 0| 0| 0| 0| 2| 0| 0| 0| 111000|2025-04-23 01:51:12.000|
PRODUCTS_PROD_CAT_IX|SH | 0| 0| 0| 17| 0| 0| 0| 442| 0| 0|2024-12-11 07:10:54.000|
--------------------+-----+---------------------+---------------------+------------------------+--------------------------+----------------------------+-----------------------------+-------------------------+---------------------------+-----------------------------+------------------------------+-----------------------+
要注意的是，这个视图里显示的是索引最后一次刷新到磁盘的使用情况，也就是15分钟内索引的使用情况。
3 索引使用追踪的局限性
3.1 有些索引的使用追踪不到
有一种索引的使用情况oracle的索引跟踪监控不到，这就是避免表锁的外键索引，外键索引有时不是用来访问的，而是用于从参考表删除行时避免在子表上加TM Share lock锁的，当参考表的行数比较少，外键索引可能不是用来访问或者子表的行，这时对子表的全表扫描可能更快，而是避免对子表加全表锁。这种情况下的外键索引是不能删除的。
3.2 有些索引的使用被误判
在对索引进行统计信息收集时，索引使用情况跟踪也会更新相应索引的信息，这不是我们想要的结果，毕竟索引是否使用，其统计信息总可能被收集的。看一下下面的示例
--V$INDEX_USAGE_INFO视图
-------------------+---------------------------+-----------------+----------------+--------------+-----------+--------------------+-----------------------+----------+------+
INDEX_STATS_ENABLED|INDEX_STATS_COLLECTION_TYPE|ACTIVE_ELEM_COUNT|ALLOC_ELEM_COUNT|MAX_ELEM_COUNT|FLUSH_COUNT|TOTAL_FLUSH_DURATION|LAST_FLUSH_TIME |STATUS_MSG|CON_ID|
-------------------+---------------------------+-----------------+----------------+--------------+-----------+--------------------+-----------------------+----------+------+
1| 0| 0| 9| 30000| 20| 1787241|2025-04-23 06:21:50.122| | 3|
-------------------+---------------------------+-----------------+----------------+--------------+-----------+--------------------+-----------------------+----------+------+
--DBA_INDEX_USAGE视图中关于I_TEMPL_DEPT的信息
SELECT * FROM DBA_INDEX_USAGE WHERE name='I_TEMPL_DEPT';
---------+----+-----+------------------+----------------+-------------------+---------------------+---------------------+------------------------+-------------------------+--------------------------+---------------------------+----------------------------+-----------------------------+-----------------------------+------------------------------+---------+
OBJECT_ID|NAME|OWNER|TOTAL_ACCESS_COUNT|TOTAL_EXEC_COUNT|TOTAL_ROWS_RETURNED|BUCKET_0_ACCESS_COUNT|BUCKET_1_ACCESS_COUNT|BUCKET_2_10_ACCESS_COUNT|BUCKET_2_10_ROWS_RETURNED|BUCKET_11_100_ACCESS_COUNT|BUCKET_11_100_ROWS_RETURNED|BUCKET_101_1000_ACCESS_COUNT|BUCKET_101_1000_ROWS_RETURNED|BUCKET_1000_PLUS_ACCESS_COUNT|BUCKET_1000_PLUS_ROWS_RETURNED|LAST_USED|
---------+----+-----+------------------+----------------+-------------------+---------------------+---------------------+------------------------+-------------------------+--------------------------+---------------------------+----------------------------+-----------------------------+-----------------------------+------------------------------+---------+
--收集索引的统计信息
begin
dbms_stats.gather_index_stats('HR','I_TEMPL_DEPT');
END;
--查看V$INDEX_USAGE_INFO视图
-------------------+---------------------------+-----------------+----------------+--------------+-----------+--------------------+-----------------------+----------+------+
INDEX_STATS_ENABLED|INDEX_STATS_COLLECTION_TYPE|ACTIVE_ELEM_COUNT|ALLOC_ELEM_COUNT|MAX_ELEM_COUNT|FLUSH_COUNT|TOTAL_FLUSH_DURATION|LAST_FLUSH_TIME |STATUS_MSG|CON_ID|
-------------------+---------------------------+-----------------+----------------+--------------+-----------+--------------------+-----------------------+----------+------+
1| 0| 1| 10| 30000| 20| 1787241|2025-04-23 06:21:50.122| | 3|
-------------------+---------------------------+-----------------+----------------+--------------+-----------+--------------------+-----------------------+----------+------+
--ACTIVE_ELEM_COUNT值变为1，待到刷新后查询DBA_INDEX_USAGE视图，可以看到统计信息分析时对索引的使用
---------+------------+-----+------------------+----------------+-------------------+---------------------+---------------------+------------------------+-------------------------+--------------------------+---------------------------+----------------------------+-----------------------------+-----------------------------+------------------------------+-----------------------+
OBJECT_ID|NAME |OWNER|TOTAL_ACCESS_COUNT|TOTAL_EXEC_COUNT|TOTAL_ROWS_RETURNED|BUCKET_0_ACCESS_COUNT|BUCKET_1_ACCESS_COUNT|BUCKET_2_10_ACCESS_COUNT|BUCKET_2_10_ROWS_RETURNED|BUCKET_11_100_ACCESS_COUNT|BUCKET_11_100_ROWS_RETURNED|BUCKET_101_1000_ACCESS_COUNT|BUCKET_101_1000_ROWS_RETURNED|BUCKET_1000_PLUS_ACCESS_COUNT|BUCKET_1000_PLUS_ROWS_RETURNED|LAST_USED |
---------+------------+-----+------------------+----------------+-------------------+---------------------+---------------------+------------------------+-------------------------+--------------------------+---------------------------+----------------------------+-----------------------------+-----------------------------+------------------------------+-----------------------+
87923|I_TEMPL_DEPT|HR | 1| 1| 59| 0| 0| 0| 0| 1| 59| 0| 0| 0| 0|2025-04-23 06:36:51.000|
---------+------------+-----+------------------+----------------+-------------------+---------------------+---------------------+------------------------+-------------------------+--------------------------+---------------------------+----------------------------+-----------------------------+-----------------------------+------------------------------+-----------------------+
4 小结
不论是Oracle 12.2 之前的索引监控还是之后的索引追踪，虽然提供了索引使用情况的信息，都不可以作为判断索引是否使用的绝对条件，只能作为判断的参考依据，要做准确的判断，还是应该结合业务方面来综合考虑，外键上的索引总是不能删除差不多可以作为一条准则吧。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
