This discussion is archived
6 Replies Latest reply: Jun 30, 2011 5:07 AM by EdStevens RSS

find index fragmentation

609621 Newbie
Currently Being Moderated
Dear all,

10.2.0.4 on solaris 10


Am using the below commands to find the index fragmentation and rebuild accordingly

analyze index medt.crm_subscriber_x3 validate structure;
select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;
 alter index medt.crm_subscriber_x3 rebuild online;
Repeating this for each and every index in the database is tedious, is there any other
way I can find out the indexes which need to be rebuild ?

I googled and checked in the forums for this info, but I couldn't find any. Appreciate any pointers and info

Thanks
Kai
  • 1. Re: find index fragmentation
    Justin Cave Oracle ACE
    Currently Being Moderated
    First off, the criteria you have is not valid. See this discussion on why relying on del_lf_rows is incorrect.

    Second, in general, indexes in Oracle do not need to be rebuilt. There are no generally applicable criteria for determining whether an index might be one of the rare ones that benefit from a rebuild that do not involve a human analyzing the expected future lifecycle of the data in the table.

    Third, can you define exactly what you mean by "fragmentation"? An index in Oracle cannot be fragmented for most definitions of that term.

    Justin
  • 2. Re: find index fragmentation
    864403 Explorer
    Currently Being Moderated
    Personally from years of index rebuild experience, Index rebuilds provide significant performance improvements considering that we have very strict millisecond SLA's. This is applicable mostly to indexes that are subjected to high inserts/deletes. We don't use any formula but try to squeeze in index rebuilds whenever we have an outage (We don't perform online index rebuilds due to strict SLA's ) and we have been doing this for years and notice consistent performance gains every time we do it.

    If your index is subjected to high inserts/deletes , then the indexes tend to grow over a period. In such case, the fact that index rebuilds reduces the foot print of indexes itself provides performance benefits because of reduced IO.

    Again every application is different , every index is different ; Great if you can gain anything from user experience in this forum

    thanks
    http://swervedba.wordpress.com/
  • 3. Re: find index fragmentation
    864403 Explorer
    Currently Being Moderated
    Personally from years of index rebuild experience, Index rebuilds provide significant performance improvements considering that we have very strict millisecond SLA's. This is applicable mostly to indexes that are subjected to high inserts/deletes. We don't use any formula but try to squeeze in index rebuilds whenever we have an outage (We don't perform online index rebuilds due to strict SLA's ) and we have been doing this for years and notice consistent performance gains every time we do it.

    If your index is subjected to high inserts/deletes , then the indexes tend to grow over a period. In such case, the fact that index rebuilds reduces the foot print of indexes itself provides performance benefits because of reduced IO.

    Again every application is different , every index is different ; Great if you can gain anything from user experience in this forum

    thanks
    http://swervedba.wordpress.com/
  • 4. Re: find index fragmentation
    Dom Brooks Guru
    Currently Being Moderated
    http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth-ii.pdf
  • 5. Re: find index fragmentation
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    swervedba wrote:
    Personally from years of index rebuild experience, Index rebuilds provide significant performance improvements considering that we have very strict millisecond SLA's. This is applicable mostly to indexes that are subjected to high inserts/deletes. We don't use any formula but try to squeeze in index rebuilds whenever we have an outage (We don't perform online index rebuilds due to strict SLA's ) and we have been doing this for years and notice consistent performance gains every time we do it.

    If your index is subjected to high inserts/deletes , then the indexes tend to grow over a period. In such case, the fact that index rebuilds reduces the foot print of indexes itself provides performance benefits because of reduced IO.
    That sounds like you may be hitting the index ITL bug - it was wasy to bypass before 10g by setting MAXTRANS on the index, but you can't do that (legally) in 10g because MAXTRANS is ignored ( http://jonathanlewis.wordpress.com/category/oracle/indexing/index-explosion/ ). It's quite easy to end up losing 50% of the index leaf block space to ITL entries before you start worrying about the typical 70% utilisation figure of B-trees, and if you've adopted the usual "fat-indexing" to hit your millisecond performance then it's not entirely surprising that you want to keep rebuilding.

    Another problem with high insert/delete rates appears with very small indexes, of course. If you have a table that is very small, but constantly recycles its space you may have an index that is close to the border between blevel = 1 and blevel = 2. If it crosses that border occasionally - and the statistics are updated to reflect the change, which is likely if (a) you have automatic stats collection enabled and (b) the table is subject to lots of updates and deletes - then execution plans could change, resulting in changes in performance. The workaround is fairly obvious - don't let Oracle collect stats automatically on that table - lock the stats, and run your own code to deal with the stats, making sure that you overwrite the index blevel with 1 even if it has just crossed the boundary to 2.


    Regards
    Jonathan Lewis
  • 6. Re: find index fragmentation
    EdStevens Guru
    Currently Being Moderated
    KaiS wrote:
    Dear all,

    10.2.0.4 on solaris 10


    Am using the below commands to find the index fragmentation and rebuild accordingly

    analyze index medt.crm_subscriber_x3 validate structure;
    select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;
    alter index medt.crm_subscriber_x3 rebuild online;
    Repeating this for each and every index in the database is tedious, is there any other
    way I can find out the indexes which need to be rebuild ?

    I googled and checked in the forums for this info, but I couldn't find any. Appreciate any pointers and info

    Thanks
    Kai
    At the risk of enabling you to complete a useless exercise in index rebuilding - which others are currently addressing in this thread - I'd like to offer a well known solution to your more general problem.

    What you are really saying is "I have a query to run against a single object, but I need to run it against hundreds of similar objects."

    The solution is to write a query that writes a query.

    Here's a simple example.
    spool doit.sql
    select 'select count (*) from '|| table_name || ';'
    from all_tables;
    spool off
    Examine the contents of doit.sql to remove any extraneous stuff, then execute it.

    I leave expansion and refinement of the technique as an exercise for the student.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points