Rebuilding Indexes
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Rebuilding Indexes

  1. #1
    Join Date
    Nov 2002
    Location
    Singapore
    Posts
    3

    Lightbulb Rebuilding Indexes

    DBA Gurus,

    I am responsibble for maintening Oracle Application [11.5.9] database [9.2.0.3]. As DBA I know when & how should we rebuild index. Can any body share their practices for rebuilding indexes reguraly. How much performance benefit we can get by rebuilding exercise especially when the rows in most tables are not deleted often. Some notes on Metalink say that index building is not really require unless there is bulk row deletes from table. And even that case we might consider reorganising table also.

  2. #2
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Monitor your index size and find out the hot indexes. You can write small script to capture index sizing details and insert them into a table and put this script in dbms_job at one day interval. You can query this table to get the trend analysis to find out the hot index.

    Just capture the details using the following query..

    select owner, segment_name, bytes/1024/1024, extents, segment_type, partition_name, tablespace_name
    from dba_segments
    /

    put all these details into a table and you can query the table later to find out the hot indexes on the basis of no. of extents it has wrapped over a period of time..
    -nagarjuna

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Rebuilding indexes is unlikely to bring you any performance benefits, and may potentially degrade performance until the index has regrown to it's "natural" size.

    If you want to recover space due to a high number of deletes, use coalesce, not rebuild.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    and may potentially degrade performance until the index has regrown to it's "natural" size.
    Well not really on Queries, but for DML may be..
    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"

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Dave's right though some indexes like to be fat. The last company I was at we were running Oracle Apps 11.54. They paid to have Tom Kyte to come in and let us DBA's fire off questions at him.

    Anyway, he was basically saying the samething that Dave said.
    We were re-building indexes every Saturday night. Performance would be slacking up till around Wednesday then it started to get better. We were stumped until Tom mentioned it to us.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by OracleDoc
    We were re-building indexes every Saturday night.
    Rebuilding index every week would be a dumb idea.. it should be carefully analyzed whether any index needs rebuild or not..
    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. #7
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Originally posted by abhaysk
    Rebuilding index every week would be a dumb idea.. it should be carefully analyzed whether any index needs rebuild or not..
    Let me refraze that. We had indexes on tables that were getting very heavy DML hence the reason for thd rebuild.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by OracleDoc
    Let me refraze that. We had indexes on tables that were getting very heavy DML hence the reason for thd rebuild.
    That will even more worse things if rebuilt once in week..

    ( fact is immediately after rebuild there will be too much of redo generation due to block splitting )..


    Well i would consider rebuild once in a year or at max twice (or when blevel gets more than 3).. its not an attempt to get better performace, but some wasted space.. Yes Queries will get some better perfromcance especially where index FFS and range scans are involved..

    Abhay.
    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"

  9. #9
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    That will even more worse things if rebuilt once in week..

    ( fact is immediately after rebuild there will be too much of redo generation due to block splitting )..


    Well i would consider rebuild once in a year or at max twice (or when blevel gets more than 3).. its not an attempt to get better performace, but some wasted space.. Yes Queries will get some better perfromcance especially where index FFS and range scans are involved..


    On what basis you are saying one year or six months?? whould you go on rebuilding indexes if they dont take any DMLs at all? and would you wait for one year if the indexed columns are under going too many DMLs?? wont you analyze the growth of your index segments (with respect to extentsor bytes) and plan for rebuild? I will two examples..

    We had one bitmap index that has the size of 450mb when we just created it and the table size being 8GB. The index grows to 7GB within a month time. We marked this as hot index and scheduled the rebuild of the index every week end.

    On the other hand, we had many indexes thay have never grown. We dont touch these indexes at all.
    Last edited by pando; 04-26-2004 at 12:47 PM.
    -nagarjuna

  10. #10
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    My point was that we were mis-informed about index rebuild and thought rebuilding indexes once a week was a good thing. It's in the past abhaysk, no need to expand on the subject.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

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