查看一个表上索引一段时间的使用情况

select d.object_name, sum(se.executions_delta) exec_counts

from DBA_HIST_SQL_PLAN d, dba_hist_sqlstat se,dba_hist_snapshot sh

where d.object_name in

    (   select index_name from dba_indexes wheretable_name=upper('GV_WORK_ORDER_ITEM') andowner='WMS')

and se.plan_hash_value=d.plan_hash_value

and se.sql_id=d.sql_id

and se.snap_id=sh.snap_id

and sh.begin_interval_time >=to_date('2015010118:00:00','yyyymmdd hh24:mi:ss')

and sh.begin_interval_time< to_date('2015013118:00:00','yyyymmdd hh24:mi:ss')

group by d.object_name

order by sum(se.executions_delta) ;