Finding Unused Indexes in Oracle

One area I think SQL Server really does a better job than Oracle is in the DMV's around indexes.  SQL Server makes it very easy to see what indexes it thinks are missing and also gives you the DMV dm_db_index_usage_stats which reports how often an index has been used and how often it has been updated.  Of course the point here is to find indexes that are not used and drop them and evaluate indexes that are only infrequently used and see if there is really a justification for those indexes to exist.

Oracle does make a lot more information available via AWR, but AWR requires you to have a diagnostics pack and tuning pack license.  So not everyone is going to have those.

We can use regular V$ views though to recover some of this functionality.  The following query uses the V$sql_plan table to find all the usages of an index, and then joins that information to the all_indexes view.  So we can see what indexes are getting used, but what statements and what indexes aren't getting used with this query.


 WITH index_usage AS

(

    SELECT pl.sql_id, pl.object_owner, pl.object_name, pl.operation,

            pl.options, count(1) as use_count

        FROM v$sql_plan pl

        WHERE pl.operation = 'INDEX'

        GROUP BY pl.sql_id, pl.object_owner, pl.object_name, pl.operation, pl.options

)

SELECT

        ix.table_owner, ix.table_name, ix.index_name, iu.operation,

        iu.options, ss.sql_text, ss.executions

    FROM all_indexes ix

    LEFT OUTER JOIN index_usage iu

             ON ix.owner = iu.object_owner

             AND ix.index_name = iu.object_name  

    LEFT OUTER JOIN v$sqlstats ss

        ON iu.sql_id = ss.sql_id

    WHERE ix.owner = '<<owner name>>'

    ORDER BY ix.table_name, ix.index_name;

Now this query is not perfect.  Since it is using v$sql_plan, you are limited to what statements are in the shared sql area right now.  But it does give you a start.  And you could always schedule this query to run on an hourly basis and dump those results to a table.  After a few days, you would have a pretty good idea of your index usage patterns.