Check fragmentation of indexes for schema by analyze command in Oracle

We need to create script for analyze the command and check health from INDEX_STATS because INDEX_STATS contain only one row after each analyze command.
So after every analyze command we need to run the select on INDEX_STATS table.

INDEX_STATS will provide the internal information of B-TREE index which help to determine the index need to rebuild or not.

INDEX_STATS column details:
HEIGHT – HEIGHT greater than 3 value, candidate for rebuild.
DEL_IF_ROWS – No of deleted leaf rows from the Index.
LF_ROWS- No of Leaf rows.
DISTINCT_KEYS – No of distinct key values in the index. Value less than 20 will be candidate for rebuild.

Following are the methods to execute the analyze command for schema

Method steps:
1. Generate script of all indexes present in schema.
2. Enable the spool in sqlplus and execute the script for the indexes generated in step 1.
3. Then you get the spool which will give you the indexes name which need to rebuild from INDEX_STATS table.

1. Generate script of all indexes present in schema.a

set echo off
set termout off
set verify off
set trimspool on
set feedback off
set heading off
set lines 300
set pages 0
set serverout on
spool D:\rman\analyze_Schema_indexes.txt
begin
for x in (select index_name from user_indexes where index_type = 'NORMAL' order by index_name)
loop
dbms_output.put_line('---- '||x.index_name||' -----');
----dbms_output.put_line('ANALYZE INDEX ' || x.index_name || ' COMPUTE STATISTICS;');
dbms_output.put_line('ANALYZE INDEX "' || x.index_name || '" VALIDATE STRUCTURE;');
dbms_output.put_line('SELECT name, height, lf_rows, del_lf_rows, round((del_lf_rows/lf_rows)*100,2) as ratio FROM index_stats where (lf_rows > 100 and del_lf_rows > 0) and (height > 3 or ((del_lf_rows/lf_rows)*100) > 20);');
end loop;
end;
/
spool off


2. Enable the spool in sqlplus and execute the script for the indexes generated in step 1

--Connect with user
--Enable the spool
Spool D:\rman\rebuild_index.txt

-- spool off
spool off


3. You get the spool file which index need to rebuild.

-- rebuild the index
ALTER INDEX "index_name" REBUILD;
-- after rebuild compute the stats for the index
ANALYZE INDEX "index_name" COMPUTE STATISTICS;