DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, July 14, 2018

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;

No comments: