Home > Performance > Index Monitoring

Index Monitoring

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 doesn't always mark an index as used even if it is used. If it isn't used in an execution plan but is used to enforce a foreign key or unique constraint, it won't get flagged as used.
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 (assumes no concatenated PK's - if you have that, the query gets more complicated):

SELECT 'ALTER INDEX '||ic.index_name||' MONITORING USAGE;'
  FROM all_ind_columns ic, all_indexes i
 WHERE i.uniqueness = 'NONUNIQUE' --don't monitor unique indexes
   AND i.table_owner = 'SCHEMA_OWNER_HERE'
   AND ic.index_owner = i.owner
   AND ic.index_name = i.index_name
   AND ic.position = 1
   AND NOT EXISTS (SELECT 'x' --Don't monitor indexes on FK's
                     FROM all_cons_columns cc, all_constraints c
                    WHERE ic.table_name = cc.table_name
                      AND ic.column_name = cc.column_name
                      AND c.constraint_name = cc.constraint_name
                      AND c.constraint_type IN ('R'));

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'),
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
       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      


Granville Bonyata
About these ads
Categories: Performance
  1. Thyge G. Jacobsen
    March 14, 2012 at 5:51 am

    The:

    AND ic.position = 1

    should be:

    AND ic.column_position = 1

    Regards
    Thyge

  2. heliodias
    May 11, 2012 at 5:47 pm

    Hi , I´d find similar issues regarding foreign keys.
    Very very weird, If you for instance, delete from the parent table but don´t have a child row (even though Oracle it is using the child index) it doesn´t show up at the v$object_usage.
    But after I tried to delete a parent record with child row associated, then the error arise and the V$OBJECT_USAGE, suddenly start to showing the use.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 47 other followers

%d bloggers like this: