Unused Indexes | Oracle | Idexes That are not used | Index usage

Unused Indexes | Oracle | Idexes That are not used 

This query is used to obtain unused indexes on Oracle database. You should rebuild this query for Your own puproses, and You have to remember that dba_hist_sql_plan table is a part o tunning pack that is additional charged.

The oracle mechanism - index monitoring is really cool - if You have unbeliveble certainty that this will not fail.


The idea is simple. Query the usage of indexes in sqlplans stored in shared memory ( v$sql ) or check this plans in awr snaps and/or compare this with segments statistics.


 select  v1.owner,index_name,value
    from dba_indexes v1 ,  v$segment_statistics v2
        where v1.index_name = v2.object_name
            and v1.index_name not in (select distinct(object_name) from (    select object_name
                                        from  dba_hist_sql_plan
                                            where object_type like 'INDEX'
                                                and object_owner like ''
                                                union all
                                     select  object_name
                                        from v$sql_plan
                                         where object_type like 'INDEX'
                                                and object_owner like '' ) )
            and v1.owner like ''  
            and v2.statistic_name like '%logical reads%' order by 3 desc ;



Cheers.
K./