Is My Index Used

I am in Manchester. The UKOUG tech13 seminar super Sunday information overload gone. Three days still left. I got finally to meet Kyle Hailey. The man who visualized the ash. Even he and Doug Burns could not find the SQL query that has created the lock in a blocking session. Another a bit similar question that is often asked. Is an index used. You could set up monitoring on an index.

ALTER INDEX index_name_here MONITORING USAGE;

And after a while you can browse USED column of the V$OBJECT_USAGE view.
It tels is your undex been used. But it does not tell if the index is used wisely. ASH to the rescue. Possibly increase the time ash is stored. And here is a query that shows the clauses that have been using the index.

select sql_id,TO_CHAR(substr(sql_text,1,4000)) txt
  from dba_hist_sqltext where sql_id in ( 
  select sql_id 
    from dba_hist_sql_plan 
   where object_name = :index_name)
order by txt
;

If no index usage is noticed. The index is a possible candidate for dropping away. The case in V$OBJECT_USAGE.

But it might be so that the index is used in a non wisely manner. From there on you can interpret the plans. It might be that the full table scan would perform better.

And going to 12c there becomes the adaptivity of a cursor execution that will end up in the plan even it executes better in other execution paths. This was covered yesterday quite nicely by Nicolas Jardot. So for 12c the is the index used query here should be attached to dba_hist_active_sess_history. Future work to be done.

Before dropping consider making the index invisible for awhile.