Index Monitoring, Status and Usage (Related to 10g)

SQL engine retain all indexes clear beside a table in spite of their usage. Normally Index maintenance can cause major amounts of CPU and disk I/O usage, which can be harmful to the performance.

Keeping this thing in mind it makes sense to identify and remove any indexes that are not in use. Index monitoring allows unused indexes to be recognized accurately, removing the risks allied with dropping useful indexes.

It is always important to make sure that index monitoring is performed over a period of time. If you only monitor indexes during specific time frames you may incorrectly show up indexes as being unused.

INDEX Monitoring

ALTER INDEX INDEX_IMERGE MONITORING USAGE;

ALTER INDEX INDEX_IMERGE NOMONITORING USAGE;

INDEX Status

SELECT TABLE_NAME, INDEX_NAME, MONITORING FROM V$OBJECT_USAGE WHERE TABLE_NAME = UPPER('&1') AND INDEX_NAME = DECODE(UPPER('&2'), 'ALL', INDEX_NAME, UPPER('&2'));

INDEX Usage

SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORIN FROM V$OBJECT_USAGE WHERE INDEX_NAME =’INDEX_IMERGE’ ORDER BY INDEX_NAME;