未使用的索引（unused Indexes），是否有价值呢？
1：浪费存储空间，尤其是大表的索引；
2：加重DML操作（UPDATE、INSERT、DELETE）的开销。
监控索引使用情况的功能:
ALTER INDEX <index_name> MONITORING USAGE;
CREATE TABLE TEST
(
ID NUMBER(10),
NAME VARCHAR2(32)
);
CREATE INDEX IDX_TEST_ID ON TEST(ID);
INSERT INTO TEST
SELECT 1001, 'Kerry' FROM DUAL UNION ALL
SELECT 1002, 'Ken' FROM DUAL UNION ALL
SELECT 1003, 'Jimmy' FROM DUAL UNION ALL
SELECT 1004, 'Jack' FROM DUAL;
COMMIT;
execute dbms_stats.gather_table_stats(ownname => 'NICK', tabname =>'TEST', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
启用对索引IDX_TEST_ID的监控
ALTER INDEX IDX_TEST_ID MONITORING USAGE;
此时观察V$OBJECT_USAGE 表数据的变化，如下所示，MONITORIN字段值变为YES，表示索引IDX_TEST_ID已经被置于监控状态。USED字段为NO表示暂时没有SQL使用该索引
set line 200
col index_name for a20
col table_name for a10
col monitoring for a10
col used for a10
col start_monitoring for a20
col end_monitoring for a20
select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- ---------- ---------- ---------- -------------------- --------------------
IDX_TEST_ID TEST YES YES 08/31/2022 11:03:41
ALTER INDEX IDX_TEST_ID NOMONITORING USAGE;
select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------- ---------- ---------- ---------- -------------------- --------------------
IDX_TEST_ID TEST NO YES 08/31/2022 11:03:41 08/31/2022 11:09:20
注意：SELECT * FROM V$OBJECT_USAGE; 只能查看当前用户下被监控的索引信息。即使sys、system用户也不能查看其它用户的信息。
如果我们想在系统中监控所有的索引，那么我们可以通过下面脚本实现监控数据库所有的索引。注意我们要排除一些系统表的索引、以及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
ORA-00701: object necessary for warmstarting database cannot be altered
00701. 00000 - "object necessary for warmstarting database cannot be altered"
*Cause: Attempt to alter or drop a database object (table, cluster, or
index) which are needed for warmstarting the database.
*Action: None.
