This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jun 30, 2008 7:33 AM by 585319 RSS

Weekly rebuilding indexes

620661 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Instead of weekly rebuild a weekly analyze may make more sense.

    Thanks.
    Ankit.
  • 3. Re: Weekly rebuilding indexes
    620661 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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