V$OBJECT_USAGE

Oracle 11gR1

V$OBJECT_USAGE displays statistics about index usage gathered from the database. You can use this view to monitor index usage. All indexes that have been used at least once can be monitored and displayed in this view.

Column	Datatype	Description
INDEX_NAME	VARCHAR2(30)	Index name in sys.obj$.name
TABLE_NAME	VARCHAR2(30)	Table name in sys.obj$.name
MONITORING	VARCHAR2(3)	YES| NO
USED	VARCHAR2(3)	YES| NO
START_MONITORING	VARCHAR2(19)	Start monitoring time in sys.object_stats.start_monitoring
END_MONITORING	VARCHAR2(19)	End monitoring time in sys.object_stats.end_monitoring

Note:

Turn on monitoring for the index:

alter index test_ids monitoring usage;

Make sure the index is being monitored:

select * from v$object_usage;

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
TEST_IDS	TEST	YES	NO	07/31/2008 11:39:32	NULL

select * from test where tt='tt';

select * from v$object_usage;

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
TEST_IDS	TEST	YES	YES	07/31/2008 11:39:32	NULL

The column USED will tell us if this index is accessed to satisfy a query.

Turn off monitoring for the index:

alter index test_ids nomonitoring usage;

select * from v$object_usage;

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
TEST_IDS	TEST	NO	YES	07/31/2008 11:39:32	07/31/2008 11:46:49

Oracle dynamic performance views