Performance drag on alter index coalesce?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Performance drag on alter index coalesce?

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    Performance drag on alter index coalesce?

    What's the performance drag on "alter index R393k3h4 coalesce;"?

    R393k3h4 has 40 gig of sequential values and at least 1/2 of the keys have been deleted. I don't want to rebuild, just make the space reusable.

    Ken

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Why not rebuild?
    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"

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    not sure if coalesce can improve performance but I guess it will lead to less index splits since there are freespaces for new index entries

  4. #4
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Good question abhaysk.

    Rebuilding the index isn't looking like our best option because:

    1. The key is too big to rebuild online.
    2. Downtime on this table has a suprisingly direct effect on stock price, happy users and my resume.

    Seriously, after a recent archival process, I have many sparsely populated index blocks and I understand that coalesce will merge these and free up empty blocks for (sequential index) re-use.

    I'm just curios how resource demanding coalesce will be.

    I'm also curious if I can stop the process and restart it later.

    -Ken

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    cant you rebuild online?

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Pando,

    I'm not having a problem with index splits because this is a sequential index. My indexes are simply growing into new blocks.

    I'm most interested in re-using sparcely populated blocks.

    Guess you posted before getting a chance to read the "key too big" reason for not rebuilding online.

    Yea, my first impulse was to rebuild online to a new tablespace.

    -Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    rebuild online may not be advisible here.. due to 2 reasons

    * online rebuild triggers FTS.. and since you say ur index if of the order 40GB, i suspect your table might be much bigger.. may be of order 150GB?

    * online rebuild will have to record all the changes in the table during rebuild and may choke up ur DMLs..


    Rebuild parallelly would be good option, as oracle reads existing index with FFS option. But draw back is it will hold locks for any DMLs and has to wait until rebuild completes.. You can rebuild when there is minimal DML activity on this table.. or better when there is no DML activity.


    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"

  8. #8
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Abhay, Yea, all that, and it's not possible because the key is bigger than the block size.

    OK, let's just agree that I'm not going to rebuild. And let's assume that I wanted to, say, "alter index .. coalesce".

    How would you characterize general system load on this operation?

    A. As intense as a rebuild.
    B. Negligible. 5 Minutes max. Run it anytime.
    C. Moderate, an index that size should coalesce in a few hours.
    D. None of the above (please explain).

    Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by KenEwald
    Pando,

    I'm not having a problem with index splits because this is a sequential index. My indexes are simply growing into new blocks.

    I'm most interested in re-using sparcely populated blocks.

    Guess you posted before getting a chance to read the "key too big" reason for not rebuilding online.

    Yea, my first impulse was to rebuild online to a new tablespace.

    -Ken
    if your index is sequential, i.e increase only then I dont think coalesce can make you reuse the index blocks since coalsce only merges leaf blocks in same branch

  10. #10
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Pando,

    Sorry, Maybe I didn't mention that I recently removed about 1/2 of the keys and left the index looking like the teeth of a back-woods southern lawyer.

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