Find Index Fragmentation and Usage
To Find Index Fragmentation
~~~~~~~~~~~~~~~~~~~~~~~~~~~
analyze index <index_name> validate structure;
select round((del_lf_rows/lf_rows)*100,2) ratio, height, lf_blks, lf_rows from index_stats;
~~~~~~~~~~~~~~~~~~~~~~~~~~~
analyze index <index_name> validate structure;
select round((del_lf_rows/lf_rows)*100,2) ratio, height, lf_blks, lf_rows from index_stats;
We are comparing the ratio between no of leaf rows which have values with leaf rows which don’t have any values, if it is above 20% then it index is fragmented. index_stats table can have only one row at a time so we need to query the table after each analyse index statement. The table exists the session where the analyze statement is executed.
analyze index APPS.ADS_INDUSTRY_NAME validate structure;
To find Index Usage
~~~~~~~~~~~~~~~~~~~
alter index <index_name> monitoring usage;
~~~~~~~~~~~~~~~~~~~
alter index <index_name> monitoring usage;
Once Monitoring is enabled use the below query to find out the usage. We need to execute the command as the owner of the index to find the usage
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;
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;
Recent Posts
