First we need to start monitoring for indexes:
Execute the below query:
SQL> ALTER INDEX index_name MONITORING USAGE; Index altered. SQL> ALTER INDEX index_name1 MONITORING USAGE; Index altered.
Execute the below query:
SQL> SELECT v.index_name, v.table_name, v.monitoring, v.used, start_monitoring, end_monitoring FROM v$object_usage v, user_indexes u WHERE v.index_name = u.index_name; INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING ------------------------------ ------------------------------ --- --- ------------------- ------------------- ADD_ID_PK ADDRESS YES NO 01/26/2013 17:06:15
for sqlserver----------------------
ReplyDeleteSELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,
sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID('@@@@db_name@@@') AND sis.OBJECT_ID = OBJECT_ID('HumanResources.Employee');
GO