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”.
