oracle 监控索引使用情况

--开启索引监控
select 'alter index ' || a.index_name || '  monitoring usage; ', a.*
  from user_indexes a
 where a.table_name = 'TB_NAME'




--查看索引使用
select * from v$object_usage a where a.table_name = 'TB_NAME';




--
select * from user_ind_statistics a where a.table_name = 'TB_NAME';
select * from user_ind_columns a where a.table_name = 'TB_NAME';




--索引在哪些sql里的使用
WITH V_TEMP AS
 (SELECT A.OWNER, INDEX_NAME, INDEX_TYPE, SUM(A.bytes) / 1048576 SIZE_MB
    FROM DBA_SEGMENTS A, DBA_INDEXES B
   WHERE A.OWNER = 'OWN_NAME'
     AND B.OWNER = 'OWN_NAME'
     AND INDEX_NAME = SEGMENT_NAME
     AND B.TABLE_NAME = 'TB_NAME'
   GROUP BY A.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE)
SELECT C.*, E.BLEVEL, E.DISTINCT_KEYS, D.OPTIONS, D.TIMESTAMP, D.SQL_ID
  FROM V_TEMP C, DBA_HIST_SQL_PLAN D, USER_IND_STATISTICS E
 WHERE C.OWNER = D.OBJECT_OWNER(+)
   AND C.INDEX_NAME = D.OBJECT_NAME(+)
   AND C.INDEX_NAME = E.INDEX_NAME
 ORDER BY E.DISTINCT_KEYS, C.INDEX_NAME;