You can use Monitoring feature of Oracle to find which indexes are unused. You can run the following query to enable monitoring on an index:
ALTER INDEX index_name MONITORING USAGE;
After you enable indexing, you should wait for a while (for example 1-2 days if you think that this index should be used daily), and then you can query v$object_view to see if index is used:
1 2 3 4 5 |
SELECT * FROM v$object_usage; INDEX_NAME,TABLE_NAME,MONITORING,USED,START_MONITORING,END_MONITORING ———————————————————————————————————————- EMP_EMAIL_UK,EMPLOYEES,YES,YES,08/28/2010 22:06:23,08/28/2010 22:24:10 |
You can run the following query to disable monitoring of an index:
ALTER INDEX index_name NOMONITORING USAGE;
Disabling monitoring an index, does not change the “used” value on v$object_usage; If you query that view, you should still use the index as used. Because the “used” property shows if index is used between start_monitoring and end_monitoring period.
v$object_view shows the index monitoring information of only the current schema. As a DBA, if you want to check all indexes, you should create a new view:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE VIEW dba_object_usage (owner, index_name, table_name, monitoring, used, start_monitoring, end_monitoring ) AS SELECT do.owner, 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_objects do WHERE i.obj# = ou.obj# AND io.obj# = ou.obj# AND t.obj# = i.bo# AND ou.obj# = do.object_id; |
To enable monitoring on all indexes, run the following script:
1 2 3 4 5 6 7 8 9 |
SET heading off SET echo off SET pages 10000 SPOOL start_monitoring.sql SELECT 'alter index ' || owner || '.' || index_name || ' monitoring usage;' FROM dba_indexes WHERE owner NOT IN ('SYS', 'SYSTEM'); SPOOL off @start_monitoring.sql |
To stop monitoring on all indexes:
1 2 3 4 5 6 7 8 9 |
SET heading off SET echo off SET pages 10000 SPOOL stop_monitoring.sql SELECT 'alter index ' || owner || '.' || index_name || ' nomonitoring usage;' FROM dba_indexes WHERE owner NOT IN ('SYS', 'SYSTEM'); SPOOL off @stop_monitoring.sql |
You can run the following query to find unused indexes:
1 |
SELECT * FROM dba_object_usage WHERE used = 'NO'; |
Last thing about monitoring indexes: If you analyze an index, it will be marked as “used”.
Mike Pazda
Hemant K Chitale
Gokhan Atil
rst
Gokhan Atil
Michael
mehmet sulun
Olga
Jeff K
Gokhan Atil
Hemanth
Dusan