6 Replies Latest reply: Jun 30, 2011 7:07 AM by EdStevens RSS

    find index fragmentation

    609621
      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
          JustinCave
          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
            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
              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
                http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth-ii.pdf
                • 5. Re: find index fragmentation
                  Jonathan Lewis
                  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
                    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.