Monitor the Index Usages in SQL Query

Index Usages in SQL
In oracle to check the usage of index that is used or not in coding or any explain plain used it. We need to enable monitoring for that index. If we find that is index in not used by any execution plain then we will remove or drop that index.

Step for monitoring Index usage in Oracle

1. For enable the monitoring of Index:

ALTER INDEX index_name MONITORING USAGE;

2. Check the usage of index.

SELECT index_name,
       table_name,
       monitoring,
       used,
       start_monitoring,
       end_monitoring
FROM   v$object_usage
WHERE  index_name = 'MY_INDEX_I'
ORDER BY index_name;

3. Disable the monitoring of Index

ALTER INDEX index NOMONITORING USAGE;

Check the index fragmentation in Oracle

Following query give us the status of Index which will be the right candidate to rebuild the index in poor index quality.

set linesize 300
spool index_info.txt
SELECT i.table_name, i.index_name, t.num_rows, t.blocks, i.clustering_factor,
case when nvl(i.clustering_factor,0) = 0 then 'No Stats'
when nvl(t.num_rows,0) = 0  then 'No Stats'
when (round(i.clustering_factor/t.num_rows *100)) < 6  then 'Excellent'
when (round(i.clustering_factor/t.num_rows *100)) between 7 and 11 then 'Good'
when (round(i.clustering_factor/t.num_rows *100)) between 12 and 21 then 'Fair'
else 'Poor'
end  Index_Quality,
i.avg_data_blocks_per_key, i.avg_leaf_blocks_per_key,
to_char(o.created,&apos;MM/DD/YYYY HH24:MI:SSSSS&apos;) Created
from user_indexes i, user_objects o, user_tables t
where i.index_name = o.object_name
  and i.table_name = t.table_name
order by 1;