For those not familiar with it, index monitoring is Oracle’s way to track whether an index is being used, letting you know if it is needed.
Two things to keep in mind:
1) It gets marked as used if it is used in a query plan, but there are other reasons you may need the index. If it isn’t used in an execution plan but is used when enforcing a foreign key or unique constraint, it won’t get flagged as used but the index is still necessary.
2) The view used to look at index usage is schema specific. You may be monitoring indexes, but the indexes won’t show up in v$object_usage unless you log in as the schema owner. It’s better to go directly to the underlying query to view all monitored indexes (query below).
Since index monitoring is very low cost, it makes sense to turn it on for all candidate indexes. Indexes on FKs and unique indexes are doing work even if not used in execution plans, so they are not candidates to drop. Here’s the query to get all non-unique, non-FK indexes:
SELECT 'ALTER INDEX '||ic.index_name||' MONITORING USAGE;'
FROM all_indexes i
WHERE i.uniqueness = 'NONUNIQUE' --don't monitor unique/pk indexes
AND i.table_owner = 'SCHEMA_OWNER_HERE'
AND i.index_name IN ('INDEX1','INDEX2')
AND NOT EXISTS (SELECT 'x' --Don't monitor indexes on FK's
FROM all_constraints c
WHERE i.table_name = c.table_name
AND i.table_owner = c.owner
AND c.constraint_type = 'R'
AND NOT EXISTS (SELECT c.owner, c.table_name, cc.column_name, cc.position
FROM all_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.owner = cc.owner
MINUS
SELECT i.owner, i.table_name, ic.column_name, is.column_position
FROM all_ind_columns ic
WHERE i.index_name = ic.index_name
AND i.owner = ic.index_owner)
)
Here’s the query to look at monitored objects if you’re not logged in as the schema owner:
select d.username, io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitored,
decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou,
dba_users d
where io.owner# = d.user_id
AND d.username = 'SCHEMA_OWNER_HERE'
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#;
And here’s an example of index monitoring in action, including the Unique index usage not being flagged:
CREATE TABLE test_monitoring AS
SELECT level id, dbms_random.value(1,1000) value
FROM dual CONNECT BY LEVEL <= 5000;
Table created.
CREATE UNIQUE INDEX test_monitoring_idx ON test_monitoring(id);
Index created.
ALTER INDEX test_monitoring_idx MONITORING USAGE;
Index altered.
--Using index for PK enforcement - does not flag the index as used:
INSERT INTO test_monitoring VALUES (100,0);
INSERT INTO test_monitoring VALUES (100,0)
*
ERROR at line 1:
ORA-00001: unique constraint (BAYPAS.TEST_MONITORING_IDX) violated
SELECT index_name, used FROM v$object_usage WHERE index_name = UPPER('test_monitoring_idx');
INDEX_NAME USE
------------------------------ ---
TEST_MONITORING_IDX NO
--But we run a select that will use the index
SELECT * FROM test_monitoring WHERE id = 100;
ID VALUE
---------- ----------
100 255.5571
--And now the index shows up as used:
SELECT index_name, used FROM v$object_usage WHERE index_name = 'TEST_MONITORING_IDX';
INDEX_NAME USE
------------------------------ ---
TEST_MONITORING_IDX YES