Index Monitoring

Too many indexes may not be a good thing …
Oracle engine keeps all indexes defined on a table independently of their use.
Their maintenance can cause considerable use of CPU and I/O which can be heavy to
system performance in a high-intensity writing .
Periodically can be useful to identify and remove indexes that are not used.
Monitoring of the indices allows us to make a precise analysis.
It is important to ensure that the index monitoring is carried out for a representative
period of time (es. entire life cycle of your application).
Index monitoring is started and stopped using the ALTER INDEX command:
ALTER INDEX Index_1 MONITORING USAGE;
ALTER INDEX Index_1 NOMONITORING USAGE;
Information about the index usage can be displayed using the V$OBJECT_USAGE
view.
SELECT table_name,
index_name,
monitoring,
used
FROM v$object_usage
WHERE index_name = ‘INDEX_1′
/
TABLE_NAME          INDEX_NAME     MON USED
—————— ————— —– ——
TABLE_1 INDEX_1 YES NO