Drilling Index usage in Database - Index Administration

Drilling Index usage in Database  (Index  Administration)

Here we will discuss following about index usage:

• About Index usage
• Checking Index usage.
• Index Monitoring
• Querying  the history data

1) About Index usage

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.

2) Checking Index usage.

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) 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;

4) 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 ifoptimizer_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.