Monday, February 16, 2009

Find unused indexes with MONITORING USAGE clause

Summary
Creating an index doesn't mean that the index will be used.
To find unused indexes for a period of time in Oracle9i and afterwards you must enable the INDEX MONITORING USAGE

Example
Create a table OBJECTS with an index:
CREATE TABLE OBJECTS AS SELECT * FROM ALL_OBJECTS;
CREATE INDEX OBJECTS_IDX ON OBJECTS(OBJECT_TYPE);

Query view to see if index is monitored:
SELECT * FROM V$OBJECT_USAGE;

No monitor is enabled. Enable index monitoring and check the view if started monitoring:
ALTER INDEX OBJECTS_IDX MONITORING USAGE;
SELECT * FROM V$OBJECT_USAGE;

Now use the index with the query:
SELECT COUNT(*) FROM OBJECTS
WHERE OBJECT_TYPE = 'TABLE';

Query again to see how monitor is going.
SELECT * FROM V$OBJECT_USAGE;
  
INDEX_NAMETABLE_NAMEMONUSESTART_MONITORING
OBJECTS_IDXOBJECTSYESYES03/05/2008 11:43:55

To stop monitoring, execute:
ALTER INDEX OBJECTS_IDX NOMONITORING USAGE;

To enable and disable monitoring indexes for a specific schema, use the following 2 queries:
SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' MONITORING USAGE;'
          MONITOR_INDEX_SQL
  FROM DBA_INDEXES
 WHERE OWNER = 'AP';

SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' NOMONITORING USAGE;'
          MONITOR_INDEX_SQL
  FROM DBA_INDEXES
 WHERE OWNER = 'AP';

To drop the unused indexes (what is the meaning to support something useless?), execute:
SELECT 'DROP INDEX ' || I.OWNER || '.' || U.INDEX_NAME || ';' DROP_SQL
  FROM V$OBJECT_USAGE U, DBA_INDEXES I
 WHERE     USED = 'NO'
       AND U.INDEX_NAME = I.INDEX_NAME
       AND U.TABLE_NAME = I.TABLE_NAME;

No comments:

Post a Comment