Find unused indexes with MONITORING USAGE clause


Summary

Creating an index which doesn’t mean that the index will be used.

To find unused indexes for a period of time in ORACLE and afterwards you must enable the INDEX MONITORING USAGE

Example

Create a table TESTTABLE with an index.
CREATE TABLE TESTTABLE AS SELECT * FROM ALL_TESTTABLE;
CREATE INDEX TESTTABLE_IDX ON TESTTABLE(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 TESTTABLE_IDX MONITORING USAGE;

SELECT * FROM V$OBJECT_USAGE;

Now use the index with the query
SELECT COUNT(*) FROM TESTTABLE
WHERE object_type = ‘TABLE’;

Query again to see how monitor is going.
SQL> SELECT * FROM V$OBJECT_USAGE;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
—————————— —————————— — — ——————- ——————-
TESTTABLE_IDX                    TESTTABLE                        YES YES 03/05/2018 11:43:55

To stop monitoring execute

ALTER INDEX TESTTABLE_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;

  • July 14, 2018 | 13 views
  • Comments