Index Usage and Monitoring...

Thought of blogging about this long ago but never took a step and now is the time to put it up here instead of keep digging my repository for queries.

Creating an Index is the first solution for every developer to overcome the issue :)
But once its created its way too hard to decide whether to keep it or drop it after days go by...

How do we know if the particular index is being used or not? Big Question...

Well, one can turn on monitoring on index:

alter index index_name monitoring usage;

Once done, you can check v$object_usage to see if the index is being used or not.
Great. But how often? Sorry view wont have answer for that.

Below query helps in finding how many times the index is being used (and one can digg thru more to find the actual query also by using sql_id):

select di.table_name "Table Name", di.index_name "Index Name",
       round(sum((ds.bytes)/1024/1024),2) "Index Size -MB",
       dhsp.options "Index Operation", count(dhsp.options) "No# of Executions"
  from dba_hist_sql_plan dhsp, dba_indexes di, dba_segments ds
 where di.owner = user
   and dhsp.object_name(+) = di.index_name
   and dhsp.object_owner(+) = di.owner
   and di.index_name = ds.segment_name
   and di.owner = ds.owner
   and not exists (select 1 from dba_constraints dc where dc.constraint_name = di.index_name and dc.constraint_type = 'P')
   --and di.table_name in ('TABLE1', 'TABLE2')
 group by di.table_name, di.index_name, dhsp.options
 having count(dhsp.options) = 0
 order by 1,2,3;

As you can see, the above query is filtering for count = 0 meaning, I am only interested in showing indexes which were never used.

Note: Recently executed queries wont show up in DBA_HIST_SQL_PLAN so replace with V$SQL_PLAN. Also check the min(timestamp) from dba_hist_sql_plan to see the retention of the history.