Rebuilding Indexes PL/SQL Script - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: Rebuilding Indexes PL/SQL Script

  1. #11
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    Are you partitioning your tables? If you are deleting by date partitioning by date would make it easier to drop a partition rather than delete. even though you are being told that the sql can not change, if you can show a bad query, and how to fix the query so that it runs consistently fast without rebuilding the indexes, then you might convince the right people to allow the change.
    this space intentionally left blank

  2. #12
    Join Date
    Apr 2008
    Posts
    6
    I don't have a trace file I can post. The wait event was a db sequential read.

  3. #13
    Join Date
    Apr 2008
    Posts
    6
    Quote Originally Posted by gandolf989
    Are you partitioning your tables? If you are deleting by date partitioning by date would make it easier to drop a partition rather than delete. even though you are being told that the sql can not change, if you can show a bad query, and how to fix the query so that it runs consistently fast without rebuilding the indexes, then you might convince the right people to allow the change.

    This is a purchased application that does not support table partitioning.

  4. #14
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    Quote Originally Posted by hozay
    This is a purchased application that does not support table partitioning.
    If you paid for this then you should have support. And the vendor should spend some time on performance.

    For tables that get a lot of inserts and deletes you might consider not generating stats on those tables, and not rebuilding any indexes. No stats are usually better than bad stats.
    this space intentionally left blank

  5. #15
    Join Date
    Apr 2008
    Posts
    6
    Great suggestion! Did not even think of that. I will give that a try.

  6. #16
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    i still wonder how the heck can rebuild help in query completing faster than without?.. Ofcourse stats after rebuild of index is favoring some path (which looks to be better plan).. and then after some time if you collect stats, its not getting you that plan and instead a worse plan, if thats the case then you should start thinking, are you collecting stats properly?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #17
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by gandolf989
    No stats are usually better than bad stats.
    Specially true when your data follows normal distribution which is what Oracle assumes when no stats are available.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #18
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by abhaysk
    i still wonder how the heck can rebuild help in query completing faster than without?
    Here is a scenario, assume that...

    a) You have a large table where heavy random delete happens.
    b) You have a composite index where a sequence or a date is the first column of the index.
    c) Your queries have faulty predicates where WHERE clause is using an incomplete set of the columns of your composite index.

    As a result of this scenario, over time you are going to find that...

    1) Because of items (A) and (B) your index is getting less and less dense e.g. less and less active keys per block as time goes on.
    2) Because of item (C) your queries are forced to do range scan on the index e.g. scanning more and more blocks as time goes on.

    In this scenario your queries are going to slow down performance over time, just a bit worst every single day.

    When you rebuild your offending index guess what? index gets more dense e.g. more active keys per block then... index range scans are shorther and voila!... queries run faster.

    PS: Please don't come back with the leaf-blocks are always in balance theory; because of (B) followed by (A) this is not true for this specific index.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #19
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I always use exception of sequence/date + heavy random deletes (leading to suboptimal usage of blocks), but somehow missed to highlight that exception in the message..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #20
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I would rather use coalesce only on such indexes (sequence or date columns being first key)
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width