It seems that perfection is attained not when there is nothing more to add, but when there is nothing more to remove. – Antoine de Saint Exupéry in Terre des HommesAs SQL developers, we keep adding more and more indexes to our tables. Every time we run new queries that are potentially slow, a new index might solve the problem. But it also creates new problems. The more indexes you have, the slower your insertions and updates will become, and obviously, the more disk space your data will use. But how do we know if we can remove an existing index? How do we know if there won’t be any performance regression? In Oracle, there’s a way to answer this question! Just look at the cursor cache of your production system. In a previous blog post about UNIQUE constraints, we have added an index called “I1DATA” to our database, and we’ve run some queries that used this index. Several days later, we want to know if that index is still being used in our “production” database. Just run the following query:
This query uses Oracle’s wonderful system views, which reveal tons of information about your production system. Learn them, especially v$sql, and v$sql_plan and impress your coworkers! The above query returns something like the following data:
SELECT * FROM v$sql JOIN v$sql_plan USING (sql_id) WHERE object_name = 'I1DATA'
SQL_TEXT LAST_ACTIVE ------------------------------------------------ SELECT COUNT(*) FROM X1 JOIN Y1 ... 14.07.16 SELECT count(*) FROM x1 JOIN y1 ... 14.07.16 SELECT count(*) FROM x1 WHERE a ... 14.07.16 SELECT COUNT(*) FROM X1 WHERE A ... 14.07.16A whole lot of additional information about the actual execution plan is contained in the result set, e.g. whether the index was used for an INDEX RANGE SCAN, or something else. The interesting thing at this point is:
Yes, the index is still being used by several queries in the cursor cache… so we probably cannot delete it yet.
CautionThese views won’t tell you reliably if an index is used. They are using the cursor cache, which may be configured in various ways, including not caching cursors for too long, or not all cursors. In any case, the following rules can be established:
- If an index appears in the cursor cache several times, you shouldn’t remove it
- If an index appears in the cursor cache only once, perhaps it isn’t really needed for that particular query. Analyse
- If an index does not appear in the cursor cache, run the query again, frequently, and after some time (and if you’re sure), you can remove it
- Some indexes may have been created for reports that run very infrequently. The suggested approach won’t cover that scenario
- This information is most reliable from the production system. You cannot possibly obtain any meaningful information from your developer box
2 thoughts on “How to Know if a Given Index Can be Dropped”
What are some of the recommended resources to learn oracle/rdms stats and performance analysis?
For simple stuff, I generally recommend http://sql-performance-explained.com by Markus Winand. Use the “jOOQ” discount code to get 10% off. For more advanced stuff, I can only recommend Chris Antognini’s Troubleshooting Oracle Performance: https://www.amazon.com/Troubleshooting-Oracle-Performance-Experts-Voice/dp/1590599179