Saturday
Jan142012

Identifying Unused Oracle Indexes

I worry about all the indexes on my Oracle database. I worry I have too many indexes that are adding performance overhead to DML statements, causing me to not get the best plans, and taking up unecessary disk space.

Setting a flag to see if they're used or not when it doesn't differentiate between the index being written to or read from doesn't calm those worries. I analyze the results over time and use them to make more informed choices about my indexing strategy.

So I devised a simple query that I run periodically to determine which of my Oracle indexes are no longer being used.

I run this periodically and compare the results over time. I don't remove any indexes in production based on a single run of this query and.... Only you know how your data is being used.

Note that the tables involved are both AWR tables. Table dba_hist_sqlstat is a view that captures a subset of statistics information from V$SQL whereas the dba_hist_sql_plan contains execution plan information. Combined like this they can give you a quick-and-dirty idea of which indexes are no longer being actively used.

I've isolated the indexes to owner "APP" because all the indexes important to my application are under the same schema. I've also called out SYSTEM and SYSAUX tablespaces to be sure we don't consider any Oracle internal stuff when looking for ways to trim the fat.

UNIQUE indexes should be regarded differently so I like to call that out as well.

You may want to consider filtering out indexes by type (e.g. CLUSTER), indexes covering Foreign Key constraints, and indexes utilized by infrequent reporting.