Oracle Tips   Donald K. Burleson

Oracle Index reorganization Diagnostics

Like tables, indexes can become disorganized due to heavy DML activity.  There has been much debate in the DBA world as to what you should look for when determining if an index is in poor shape, but the script below should help. 

The script displays the level and clustering factor of the index, calculates the percentage of used extents to maximum extents, and also determines if the index can extend into its next block of free space.  For version 7 of Oracle, use the idxreorg7.sqlscript:

See Code depot for complete script

select
          owner,  
          segment_name index_name,  
          segment_type,
          round(bytes/1024,2) index_kb, 
          clustering_factor,
. . . .

select 
         a.owner owner,  
         segment_name,
         segment_type,
         bytes,  
. . .

from
         sys.dba_segments a,  
         sys.dba_indexes b,
         sys.ts$ c
   where
         ( a.owner = b.owner ) and 
         ( segment_name = index_name ) and 
         ( ( segment_type = 'INDEX' ) ) and
         b.tablespace_name = c.name),
(  select
    tablespace_name f_tablespace_name,
    max(bytes) max_free_space
   from
    sys.dba_free_space
    group by tablespace_name)
where
    f_tablespace_name = o_tablespace_name
order

    by 1,2;

 

For Oracle8 and higher, use the idxreorg8.sqlscript:

See Code depot for complete script

select  

         /*+ RULE */
         owner,  
         segment_name index_name,  
         segment_type,
         round(bytes/1024,2) index_kb, 
         num_rows,
         clustering_factor,
         blevel,
         blocks,  
         max_extent_pct,
. . .
from
(select 
         a.owner owner,  
         segment_name,
         segment_type,
. . .  
         sys.dba_segments a,  
         sys.dba_indexes b,
         sys.ts$ c
   where
         ( a.owner = b.owner ) and 
         ( segment_name = index_name ) and 
         ( ( segment_type = 'INDEX' ) ) and
         b.tablespace_name = c.name
 union all
 select 
         a.owner owner,  
         segment_name || '.' || b.partition_name,
. . .

where
    f_tablespace_name = o_tablespace_name
order

    by 1,2;

Seeing index levels beyond four, or bad clustering factors for indexes with supposed high cardinality , should lead you to investigate whether the index should be reorganized or even maintained in the system. 

The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.

It's only $19.95 and you can order it and get instant access to the Oracle scripts here:

http://www.rampant-books.com/book_2003_1_perf.htm

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.