Saturday, 8 February 2014

How to check Index usage specially when application is third party.

Indexes are optional object in database but they exist in each database, small or big.
Indexes only purpose is that they can fetch the data faster using the rowid or Fast Full scan method.

Fast Full Scan: when all the required columns of the query are present in the index.

To tune a query, people think about to create an Index but that's not right approach , just to tune a query do not create a index instead think of other tuning techniques

To get something you have to pay something apply to indexes , DML operation become slower as Index stores the row id along with value of the Indexed column data , Whenever a DML happens , Oracle Needs to update the Index Structure accordingly , so time consuming and Extra storage.

In this section we will not discuss when to create an Index, but we will focus on that the index which we created for our Apps are used by our application Queries or not?

There is no short cut as such to check whether your indexes are used or not, especially if your application is third party application. We have few methods by which we can conclude that our indexes are used or not and which queries & programs are using them.

 1. Index Monitoring

Easiest method is to enable the index monitoring for the all the indexes.

Alter Index |Index name| monitoring usage;

Data dictionary table: V$OBJECT_USAGE

Give the information that Index was used or not during that period in which Monitoring is Enabled.

Column Name
Self Explanatory
Yes or No
Yes or No
Start Monitoring
Start date
End Monitoring
End Date

Drawback of the method is it will not give you details which query using the index and how many time this index was used. It gives only info that Index was used but no information like once or thousand times.

To disable the Monitoring syntax.

Alter Index |Index name| no monitoring usage;

2. v$sql_plan_statistics_all Table

Querying the v$sql_plan_statistics_all table from where you can easily make decisions about the Index usage.

Few Important Columns of the Table.

Column Name
 Index, select Statement , Table Access full etc.
Yes or No
Yes or No
Start date
Self Explanatory
Input output cost
Access conditions
Filter Conditions
Columns of the sql
No of Executions
Id of the SQL statement

Query to find out the number of time Index used for a Particular query.
select count(*) "#SQL", sum(executions) "#EXE",
object_name ,options
from v$sql_plan_statistics_all
where operation = 'INDEX' and
object_owner = 'Schema Name'
and object_name in (select index_name from all_indexes where table_name IN ('xx'))
group by object_name,options
order by object_name,options

Query to find out the Access predicates and Filter predicates which is important.

select * from v$sql where sql_id in (select sql_id  from v$sql_plan_statistics_all where operation = 'INDEX' and object_owner = 'SCHEMA'     and object_name ='INDEX_NAME' and access_predicates like '%xx%' )

if you want to find the exact query in order use  V$SQLTEXT_WITH_NEWLINES instead of V$SQL.
This View gives you the information like AWR report by which you can make decision about the index usage.

3. Querying the History data 





Query to find the SQLTEXT using the Index from the history data.

select * from dba_hist_sqltext where sql_id in

 (select pl.sql_id from
 dba_hist_snapshot sn , dba_hist_sqlstat sq ,  dba_hist_sql_plan pl      
where SN.DBID=SQ.DBID and 

Now you got to know that a particular index was not in use and not useful but in production you can’t drop your index straightway because it could be risky .In 11g we have feature of Invisible indexes.

When you make an index invisible then optimizer can not see it and did't use this index except in case of if optimizer_use_invisible_indexes parameter is true which is unlikely a case, but in the background it will keep the index structure up to date.

select * from V$PARAMETER where name like '%invisible%'

Syntax for making an index invisible
alter Index Indexname invisible;

More details about Invisible indexes is on my Next Blog Diff B/w making and Index Unusable and Making and Index Invisible.

You can query DBA_VIEWS to get the information about the data dictionary views.I did this exercise to remove the unusable index from my application in this application we have lot of composite indexes on the transaction tables in some cases Index size is more then the data size.

I did't find this info at one place so i just tried to put it at one page. 

Your Questions and Suggestion are always welcome.


  1. Quite Helpful!!
    But coming back to your initial portion of the blog.."to tune a query do not create a index instead think of other tuning techniques."
    could you post something on other tuning techniques which can be helpful before going for Indexes (as last option)

    1. This comment has been removed by the author.

    2. This comment has been removed by the author.

    3. Thanks . Suppose my table is very small or my queries are accessing large portion of data blocks in this case index range scan is costlier instead of full table scan. So instead of cresting an index we should go for using the non standard db size blocks like 16k for thi
      S so that less input output required.

  2. Thank you for posting such a useful, impressive and informative content.
    You can also see my blog
    how to Clone database from physical standby as target in oracle.