select *

Performance and Troubleshooting inside Oracle


Is my index really used ?

I’d like to get some feedback on the methods you are using to check if a particular index is used or not. This is something I do quite often when I see a table slightly overindexed (does this word exist by the way ?) and that I doubt that all indexes are really useful. You’ll probably immediatly think about the MONITORING clause but I’ll say a word on that below.

What I usually do is (providing that the AWR retention period  is set high enough) check DBA_HIST_SQL_PLAN, and filter on the OBJECT_NAME column with the name of the index (in uppercase as always). The rows you get in that case are steps of execution plans that cannot be used, but the only column that you need is SQL_ID, you get the list of SQL_ID that have been captured by the AWR and are somewhere in the execution plan using this index. With this list you can then query DBA_HIST_SQL_TEXT and start to check the queries that are using the index.

Displaying the SQL_ID that have used a specific index during the AWR retention period
select distinct(sql_id) from DBA_HIST_SQL_PLAN
where object_name = 'MGMT_TARGETS_IDX_01';
Retrieving the SQL_TEXT for these queries
with list as ( select distinct(sql_id) from DBA_HIST_SQL_PLAN
where object_name = 'MGMT_TARGETS_IDX_01' )
select * from DBA_HIST_SQLTEXT s, list where list.sql_id = s.sql_id;

This is useful when a table has many indexes and you doubt that some of them are really useful. Sometimes you’ll find that the index is used, but that the single SQL statement using them is the SQL used by DBMS_STATS to collect statistics.

The limitation of using this method is that it’s still possible to miss SQL statements (and this already happened to me), the SQL statements that are using the index you’re focusing on but are:

– not taking a lot of resources (logical reads, physical reads)

– not executed very often

– and therefore not captured by the AWR

This is enough to be under the AWR radar. That’s why I often do the same check but on V$SQL_PLAN with live and fresh data from the shared pool content. But you can still miss something… Since Oracle 11g it’s now possible to make an index INVISIBLE (which is by the way a great feature), this can be helpful when you want to cleanup a bit and remove unused indexes. It is still possible to set the MONITORING clause on the index, in 11g R2 this still works, but requires an ALTER INDEX (which cannot always be done on production databases), but as explained you may get USED for an index even if this one is not really used by application code. The problem with MONITORING on indexes is that you have to activate it, wait for days/weeks of activity to finally find see that all indexes have the USED status…

Does anybody have a good method for that ?