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.

Syntax:

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.

 V$OBJECT_USAGE

Column Name	Content
INDEX Name	Self Explanatory
Monitoring	Yes or No
Used	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	Content
OPERATION	Index, select Statement , Table Access full etc.
OPTION	Yes or No
OBJECT_OWNER	Yes or No
OBJECT_NAME	Start date
COST	Cost
CPU COST	Self Explanatory
IO COST	Input output cost
ACCESS PREDICATES	Access conditions
FILTER PREDICATES	Filter Conditions
PROJECTIONS	Columns of the sql
EXECUTIONS	No of Executions
SQLID	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 

DBA_HIST_SQL_TEXT

DBA_HIST_SNAPSHOT 

DBA_HIST_SQLSTAT

DBA_HIST_SQL_PLAN

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 

SN.SNAP_ID=SQ.SNAP_ID and

SN.INSTANCE_NUMBER=SQ.INSTANCE_NUMBER  

and SQ.SQL_ID=PL.SQL_ID and PL.OBJECT_NAME ='INDEX_NAME');

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.