Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2009
    Location
    US
    Posts
    29

    Unanswered: How to find fragmented indexes?

    Hi Guys

    We are running SAP on Oracle 10g on Linux ...

    One of our databases has over 100 000 indexes on it.

    1. If one considers a specific index, how can one determine if it is fragmented?

    2. If one considers all 100 000 + indexes on our database, is there a way to script something which will consider each one and determine if it is fragmented?
    Thus, after running said script one could get a list of which indexes (if any) are fragmented?

    I would like to script both (1) and (2) if possible.

    Thanks in advance!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >how can one determine if it is fragmented?
    If you need to ask this question, then the answer does not matter.

    If you can not identify a fragmented index, how/why do think they exist?
    Last edited by anacedent; 06-08-09 at 01:27.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2009
    Location
    US
    Posts
    29
    Quote Originally Posted by anacedent
    >how can one determine if it is fragmented?
    If you need to ask this question, then the answer does not matter.

    If you can not identify a fragmented index, how/why do think they exist?
    It's a question of trying to do preventative maintenance. One does not want to wait until certain indexes become so fragmented that performance is adversely affected. Ideally we would want to identify fragmented indexes sooner rather than later.

    So again i ask the question - given a specific index, how can one quantitatively measure the degree of fragmentation of that index?

    Once I have this question answered i can then proceed to write a script which will apply this 'test' to each and every index, thereby identifying which indexes require defragmentation/rebuilding.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    In oracle, typically the indexes are self maintaining and do not need to be rebuilt.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    May 2009
    Location
    US
    Posts
    29
    Quote Originally Posted by beilstwh
    In oracle, typically the indexes are self maintaining and do not need to be rebuilt.
    Thank you for that reply.
    I would hope this to be the case for such a robust database as Oracle!

    However I'd still like to know if there was a quantitative way of testing whether a specific index is fragmented?

    (Let's say I - or my boss - may be a sceptic, and would want another way to do 'spot checks')

    As an analogy if i considered a specific table i could look up (in the view dba_tables) what the average row size of the table is, and this multiplied by the row count will give me the actual data size required by the table.
    The amount of 'empty space' can then be calculated simply by subtracting this from the amount of space currently being occupied by the table. This amount of 'empty space' would thus give a measure of fragmentation of the table.

    Similarly, I'm thinking there must be some way to quantitatively analyse the degree of fragmentation of an index... ??


  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by Sandra82
    Thank you for that reply.
    I would hope this to be the case for such a robust database as Oracle!
    It is indeed. Search on asktom for index rebuild. Bottom line is, that indexes in Oracle (nearly) never need maintenance.

    However I'd still like to know if there was a quantitative way of testing whether a specific index is fragmented?
    Exactly what do you think is index fragmentation?
    If you can explain that, I'd be highly interested, because I cannot imagine any way a "fragmentation" (as seen on a harddisk) can happen in an Oracle index.

  7. #7
    Join Date
    May 2009
    Location
    US
    Posts
    29
    Quote Originally Posted by shammat
    It is indeed. Search on asktom for index rebuild. Bottom line is, that indexes in Oracle (nearly) never need maintenance.

    Exactly what do you think is index fragmentation?
    If you can explain that, I'd be highly interested, because I cannot imagine any way a "fragmentation" (as seen on a harddisk) can happen in an Oracle index.
    I found this at :
    Identify Index Fragmentation


    Identify index fragmentation

    To obtain information about an index:

    analyze index <index_name> validate structure;

    This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.

    An index should be considered for rebuilding under any of the following conditions:

    * The percentage of deleted rows exceeds 30% of the total, i.e. if
    del_lf_rows / lf_rows > 0.3.
    * If the ‘HEIGHT’ is greater than 4.
    * If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.


    I'm not sure whether this coincides with my "idea" of fragmentation simply because i'm not exactly sure what index fragmentation is. I have a high level view of it being when the index has a lot of entries which no longer point to the relevant record simply because the record has been deleted... as in the first example mentioned above.

    The point is I can gather from this post that there do seem to be circumstances when an index rebuild is warranted.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by Sandra82
    The point is I can gather from this post that there do seem to be circumstances when an index rebuild is warranted.
    To achieve what?

    Does that site show any proof that rebuilding such an index increases performance?

    Do as I suggested and search http://asktom.oracle.com for the topic. Tom Kyte is one of the leading experts in Oracle.

    Of course you can just rebuild those indexes and it surely will not do any harm.

    But the question is: what does it buy you apart from the hassle to maintain the scripts and the tedious job to monitor the indexes?

  9. #9
    Join Date
    Nov 2003
    Posts
    76
    ok since most people here think indexes are self sustaining and dont need maintenance let me ask a question like this.
    Under what circumstances should Oracle indexes be analyzed and rebuilt?

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Under what circumstances should Oracle indexes be analyzed and rebuilt?
    Any response needs to provide producible test case(s) which quantifies the (purported) benefit(s).

    >* The percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3.

    Why 30%? Why not 20% or 40%?

    >* If the ‘HEIGHT’ is greater than 4.
    Why 4? What bad happens when 5 or greater?

    >* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.

    What value qualifies as being "significantly smaller "?
    How does different DB block size affect this pronouncement?
    Does the length of a single index (which reduces the number of indexes per block) matter?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by Naweed
    Under what circumstances should Oracle indexes be analyzed and rebuilt?
    Again my question: Rebuild to achieve what?

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by Naweed
    ok since most people here think indexes are self sustaining and dont need maintenance let me ask a question like this.
    Under what circumstances should Oracle indexes be analyzed and rebuilt?
    What differentiates a PK index which "should be rebuilt" from a PK index that can be left alone? Which metric & at what value (quantify)?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Nov 2003
    Posts
    76
    I would say rebuild index to Acheive better performance. I think we meant the index depth instead of the Height. Ok never mind, lets all read this article first.

    Identifying which Oracle Indexes to Rebuild

  14. #14
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Still no proof that it changes anything.

    You probably didn't take the time to search asktom for this topic, so here are the links

    http://asktom.oracle.com/pls/asktom/...:6601312252730
    http://asktom.oracle.com/pls/asktom/...:2913600659112

    A lot more trustworthy, than dba-oracle.com

    I give up

  15. #15
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by Naweed
    I would say rebuild index to Acheive better performance. I think we meant the index depth instead of the Height. Ok never mind, lets all read this article first.

    Identifying which Oracle Indexes to Rebuild
    I would strongly suggest that if you do read that article, read this article immediately after.
    Ooops, didn't notice that you had already posted Shammat. Don't know why I didn't see your post there. Time for bed obviously.
    Last edited by pablolee; 06-09-09 at 19:05.

Posting Permissions

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