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_TY