1 2 Previous Next 17 Replies Latest reply: Jun 30, 2008 9:33 AM by 585319 RSS

    Weekly rebuilding indexes

    620661
      Hi All

      What will you say about having weekly indexes rebuilt in case of large tables where frequent (heavy) insertions and deletions takes place. For one application backend db, the vendor recommend to have 'job' which will weekly rebuild indexes for few large table. Every table has 5-10 indexes.
      Database is 10.2.0.1 and all indexes sum up to more than 40Gb.
        • 1. Re: Weekly rebuilding indexes
          181444
          For the great majority of indexes an automatic weekly rebuild would be a waste of computer resources.

          There are some cases, ususally in my experience, involving indexes built on columns whose value change frequently after creation where a rebuild will free up significant space; however, even in cases like this the time period is likely to be months, quarters, or even yearly rather than weekly.

          You are highly unlikely to see any measurable, repeatable performance improvement from index rebuilds.

          HTH -- Mark D Powell --
          • 2. Re: Weekly rebuilding indexes
            449681
            Instead of weekly rebuild a weekly analyze may make more sense.

            Thanks.
            Ankit.
            • 3. Re: Weekly rebuilding indexes
              620661
              Yeah, you are right. It doesn't make much sense to rebuild indexes weekly.
              With a SR oracle support is also suggesting (in this case) to do periodic rebuilds but they didn't specify any time period.
              But in case if we do not rebuild indexes for very long time, could there be performance degradations or it just concerns with [disk]space only?
              • 4. Re: Weekly rebuilding indexes
                585319
                Hi,

                Besides that you may be facing a leaf split overhead, which may slowdown your processes.

                I may suggest using ANALYZE INDEX ... VALIDATE STRUCTURE OFFLINE / SELECT * INDEX_STATS, and DBMS_SPACE.SPACE_USAGE to get a better idea of your index health.

                Richard Foot has very detailed material regarding indexes, you may see his blog http://richardfoote.wordpress.com/

                Regards
                Ignacio

                http://oracledisect.blogspot.com
                • 5. Re: Weekly rebuilding indexes
                  Dan A
                  If you go for the rebuild option (my DBA has me do one every couple of months or so) be sure - if you have the option / luxury - of turning off archivelog mode. I spent every 5 or ten minutes having to move the logs that were generated and it could have easily been avoided with the old ALTER DATABASE ....command.
                  • 6. Re: Weekly rebuilding indexes
                    mbobak
                    Um, Dan? Check the reference manual for the 'NOLOGGING' option of the CREATE INDEX and ALTER INDEX .. REBUILD commands.

                    No need to change database's archivelog mode.


                    -Mark
                    • 7. Re: Weekly rebuilding indexes
                      ajallen
                      You might want to consider shrink space instead of rebuild if all you want to do is coalesce partially used blocks.
                      • 8. Re: Weekly rebuilding indexes
                        620661
                        ajallen
                        What does that mean how that works out?
                        This is 10g database and Index tablespaces are obviously LMT. I didn't get you exactly. Please explain. I just have to suggest about this production db to prod-dba. I should have some facts to present. If you can suggest some links that would be really helpful.
                        After reading on Asktom and reviewing metalink reply I was a bit confused.
                        • 9. Re: Weekly rebuilding indexes
                          108476
                          Hi,
                          the vendor recommend to have 'job' which will weekly rebuild indexes for few large table.
                          You must follow your vendor guidelines to stay suported, right?

                          Oracle Press author John Garmany has a good article titled "Rebuilding Index - when why and how", that can be downloaded from the Oracle web site:

                          http://download-east.oracle.com/oowsf2004/1082_wp.pdf

                          Lt. Col. Garmany says: "The only time you need to rebuild indexes is when the cost to rebuild is less that the performance gained. The first one is obvious. Pack your tables and indexes (rebuild into the soon to be read-only tablespace) tightly and they will stay that way.

                          The second is much more difficult. First, it is a continuing process since the index will move toward fluff with use. Second, there is the cost of rebuilding the index AND the cost of the additional redo as the index changes. There is only one method to determine is rebuilding an indexes benefits your database, testing."

                          Also I have my notes here:

                          http://www.dba-oracle.com/t_index_rebuilding_issues.htm

                          See Oracle MetaLink note 122008.1 for the officially authorized script to detect indexes that benefit from rebuilding. This script detects indexes for rebuilding using these rules: Rebuild the index when these conditions are true:

                          - deleted entries represent 20% or more of the current entries.

                          - the index depth is more then 4 levels.

                          Oracle's index rebuilding guidelines appear in Metalink note 77574.1 (dated April 2007) recommends that indexes be periodically examined to see if they are candidates for an index rebuild:

                          “When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.

                          It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.”

                          The note also says that the index rebuilding criteria has changed since the advent of Oracle9i, where a blevel > 4 was a good threshold. It also notes that the pct_deleted column in index_stats from “alter index xxx validate structure” provides a guideline for index rebuilding candidates:

                          “Prior to 9i, if the BLEVEL is more than 4, it is recommended to rebuild the index. . .

                          The PCT_DELETED column shows what percent of leaf entries (index entries) have been deleted and remain unfilled.

                          The more deleted entries exist on an index, the more unbalanced the index becomes. If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding.”

                          --------------------

                          Hope this helps. . .

                          Donald K. Burleson
                          Oracle Press author
                          Author of "Oracle Tuning: The Definitive Reference":
                          http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm
                          • 10. Re: Weekly rebuilding indexes
                            Jonathan Lewis
                            >
                            Also I have my notes here:

                            http://www.dba-oracle.com/t_index_rebuilding_issues.htm

                            See Oracle MetaLink note 122008.1 for the officially
                            authorized script to detect indexes that benefit from
                            rebuilding. This script detects indexes for
                            rebuilding using these rules: Rebuild the index when
                            these conditions are true:

                            - deleted entries represent 20% or more of the
                            current entries.

                            - the index depth is more then 4 levels.

                            Oracle's index rebuilding guidelines appear in
                            Metalink note 77574.1 (dated April 2007) recommends
                            that indexes be periodically examined to see if they
                            are candidates for an index rebuild:
                            If you check Metalink, you will find that both the notes you mention are no longer available.

                            I read them Re: Leaf nodes and Blevel in an index and used the Metalink feedback forms to point out how inappropriate the content was - so they've been pulled.




                            Jonathan Lewis
                            http://jonathanlewis.wordpress.com
                            http://www.jlcomp.demon.co.uk
                            • 11. Re: Weekly rebuilding indexes
                              620661
                              Donald

                              I get it. Thanks a ton for enlightenment(s). You have made everything clear. Still your notes keeps pressing upon fact that index rebuilding should be based upon 'conditions' not 'time'. So we can design scripts in that way.
                              I do not have anything to do with putting on or off any prod jobs. Just to suggest what seems good to me. Vendor 'recommends' only for space concern, not performance, thats a different matter. Please dont mind.
                              • 12. Re: Weekly rebuilding indexes
                                585319
                                We hope that Oracle correct their content with valid and up-to-date information; you, Richard Foote and Tom Kyte have researched and published excellent material about indexes, that should be considered to reform those notes.

                                PS: I wonder why Oracle took too long to take action on this...

                                Regards
                                Ignacio

                                http://oracledisect.blogspot.com
                                • 13. Re: Weekly rebuilding indexes
                                  108476
                                  We hope that Oracle correct their content with valid and up-to-date information
                                  But nothing was updated, nor made up-to-date, just removed?
                                  I wonder why Oracle took too long to take action on this...
                                  I wonder why they no longer give any advice at all on this popular topic?

                                  Sadly, if his vendors says that they must do something to stay supported, he may not have any choice.

                                  I've seen loads of unnecessary rebuilds done because a vendor threatended to desupport the customer did they did not comply . . . .
                                  • 14. Re: Weekly rebuilding indexes
                                    585319
                                    But nothing was updated, nor made up-to-date, just
                                    removed?
                                    Yep, just removed... that's why we hope
                                    I wonder why they no longer give any advice at all on
                                    this popular topic?

                                    Sadly, if his vendors says that they must do
                                    something to stay supported, he may not have any
                                    choice.

                                    I've seen loads of unnecessary rebuilds done because
                                    a vendor threatended to desupport the customer did
                                    they did not comply . . . .
                                    That's because business software (ERPs, CRMs, BI, etc) vendors mostly design the product for one database-abstract-layer, sometimes with slight modifications to fit particular DB infrastructure. Some procedures are valid for specific DB, same are not valid for other DB, but they have generalized the relationship among Application and DB, in a one-solution fits all.

                                    It's logical, seems they are focused on business processes, not Application performance nor exploit new or exclusive DB features, that would fork their development tree without great returns.

                                    Regards
                                    Ignacio

                                    http://oracledisect.blogspot.com
                                    1 2 Previous Next