Is this index being used?

When teaching the Optimizing Oracle SQL, Intensive class I’m often asked how to tell if an index is being used or not.  Oracle does have a feature for this called (oddly enough) index monitoring.  However it’s a less than ideal.  First off it’s just a YES or NO switch.  Either the index has been used since you turned it on or it hasn’t.  Also it’s not exactly 100% accurate.   Sometimes an index will be used and not counted and other times the way it’s used isn’t really what you want, for example collecting stats will typically be counted as “using” the index.  However this is not the type of use most folks are interested in.  We’d like to know when it’s used as part of a query.

We can see this.  With a rather simple query you can easily see which indexes are in use and how many times, and even how the index is being used.  Something like this does the trick; of course you’d want to change the owner name.  

The "#SQL" column is the number of different SQL statements currently in the library cache that have used the index and the "#EXE" column shows the total number of executions.  

select count(*) "#SQL", sum(executions) "#EXE",
object_name, options
from v$sql_plan_statistics_all
where operation = 'INDEX' and
object_owner = 'OP'
group by object_name, options
order by object_name, options
/

An example run:

SQL> @index_usage

  #SQL   #EXE OBJECT_NAME              OPTIONS
------ ------ ------------------------ ------------------------
     4      4 BIG_OBJTYPE_IDX          RANGE SCAN
     2      4 EMP_EMPNO_PK             FULL SCAN
     1      3 EMP_UPPER_IDX            RANGE SCAN
     1      2 LIC_OWN_CTY_LICNO_IDX    SKIP SCAN
     1      3 SALESORDER_PK            FAST FULL SCAN
     1      2 SALESORDER_PK            FULL SCAN (MIN/MAX)
     1      1 SALESORDER_PK            RANGE SCAN DESCENDING
     1      3 SALESORDER_PK            UNIQUE SCAN
     2      2 USERNAME_PK              FULL SCAN
     2      2 USERNAME_PK              UNIQUE SCAN
SQL>

From this simple output we can see that the BIG_OBJTYPE_IDX is used in four different statements that each ran just once.  And that the SALEORDER_PK is used a variety of ways.  Each one from the same statement but some run more times then others.

A couple of other things to keep in mind, this will only show what’s happened “recently”.  Being a V$ view the data isn’t kept in here for any particular length of time.   In a production environment you might want to run this a few times in the day to capture what’s going on.  Also this view is fairly expensive to select from, so doing this once in a while is fine, just don’t set this up to poll the view every second for the day.   It will likely show us as a “top SQL” statement in every monitoring tool.