Check Index Usages and Find Index Quality Fragmentation

Check Index Usages in SQL Query and Find Index Quality

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 because it is not used in any SQL query execution plan.

We Can test the index usage that application is used it or not, so that we drop extra index which is never used by Monitoring index.

Step for monitoring Index:

1. For enable the monitoring of Index:

ALTER INDEX index 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;

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;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.