Index monitoring – things to keep in mind

by Granville Bonyata on February 17, 2012

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:

  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
                                      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,,,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitored,
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
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# =;

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.


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                                              

Previous post:

Next post: