This discussion is archived
1 2 3 4 7 Previous Next 92 Replies Latest reply: Nov 27, 2009 12:11 AM by Hemant K Chitale Go to original post RSS
  • 15. Re: corrupted indexes
    108476 Journeyer
    Currently Being Moderated
    The structure of the B*-tree is NEVER out of balance.
    Again, it depends on how you define the word "unbalanced"

    Personally, I don't care for "word games"; it only serves to confuse . . .

    Oracle indexes get unbalanced becaise Oracle uses data blocks as the boundaries for inodes, andf the index internals allow him to re-link blocks onto the freelist.

    That's why you cannot specify PCTUSED on an index. . . .

    Remember, just because a logically deleted index node MIGHT be re-used, it does not detract from the fact that the b-tree index is indeed unbalanced.

    The unbalanced index nodes are called "index browning", like a tree hit by lightening, with brown branches:

    http://asktom.oracle.com/pls/ask/f?p=4950:8:444551828551181757::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6601312252730,

    "a high percentage of deleted leaf rows to leaf rows (think of leaf rows like leaves on a tree --
    dead leafs (deleted row entries) are "brown". If you have lots of dead leaves, the tree looks
    brown)"

    From the book Oracle9i Performance Tuning: Optimizing Database Productivity, Sam Afyouni he recommnends "Monitor index browning (due to deletions; rebuild as necessary)"

    http://www.dbazine.com/oracle/or-articles/jlewis13

    "A "human" response to seeing this pattern in an index would, indeed, be to call it "unbalanced." Clearly, the right-hand side of the index is "heavier" than the left."

    Again, it's all about how you define "unbalanced". . . .

    I think that "word games" are not valuable and let's not parse word meanings here . . .

    It's an absolute truth that some high DML Oracle indexes become fragmented and require periiodic rebuilding to "clean-up" the structure and keep high performance (especially for index FFS operations).
  • 16. Re: corrupted indexes
    108476 Journeyer
    Currently Being Moderated
    Hi Mark,
    In my view, "balanced" means that the number of "hops" from the root block to any leaf block is always the same.
    Yes, I know, good point.

    But as a DBA, I look at indexes from a physical viewpoint, as arrays of pointers residing on data blocks, and from that perspective they become unbalnced when data block are yanked from the tree.
    If you have an alternate definition of "balanced", please clarify it.
    Maybe we can agree that indexes become "physically" unbalanced, and never "logicaly" unbalanced.
  • 17. Re: corrupted indexes
    sybrand_b Guru
    Currently Being Moderated
    Burleson,

    Can you please only use textbook definitions. In every IT text-book you will learn that B*-tree indices can NOT by design become unbalanced.
    And all the rest you post on this is simply incorrect. Really sorry to say so, but you are misleading and misguiding people.

    ------------------
    Sybrand Bakker
    Senior Oracle DBA
  • 18. Re: corrupted indexes
    mbobak Oracle ACE
    Currently Being Moderated
    burleson wrote:
    Hi Mark,
    In my view, "balanced" means that the number of "hops" from the root block to any leaf block is always the same.
    Yes, I know, good point.

    But as a DBA, I look at indexes from a physical viewpoint, as arrays of pointers residing on data blocks, and from that perspective they become unbalnced when data block are yanked from the tree.
    When data blocks are yanked from the tree?? How does this unbalance the index? Note, this seems to me to be a completely different issue than the "brown" leaf blocks, i.e. those with lots of deleted entries in a leaf block that's still part of the index structure. Once a leaf block is completely empty, and is "yanked from the tree" (presumably to be put back on the freelist), this is a good thing, in terms of index self-maintenance.


    -Mark
  • 19. Re: corrupted indexes
    311441 Employee ACE
    Currently Being Moderated
    Hi Sybrand

    100% agree with you.

    Here's an excellent article by Jonathan Lewis, someone who really knows what they're talking about, that explains the concept of how Oracle indexes always remained "balanced":

    http://www.dbazine.com/oracle/or-articles/jlewis13

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 20. Re: corrupted indexes
    108476 Journeyer
    Currently Being Moderated
    100% agree with you.
    We are not talking opinions here, these are facts. . . .

    Physically, Oracle indexes become unbalanced . . .

    Tell me Richard, is the Oracle segment advisor wrong when it identifies unbalanced indexes for rebuilding?

    http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2005-08-25.8847176467

    "The 10G segment advisor identifies segments that have become fragmented as a result of update and delete operations. Oracle describes these objects as being sparsely populated. Not only do sparsely populated objects waste space but they can also contribute to SQL performance problems. "

    Also, see right here, on OTN:

    http://www.oracle.com/technology/oramag/oracle/05-may/o35tuning.html
  • 21. Re: corrupted indexes
    sybrand_b Guru
    Currently Being Moderated
    Yes, these are facts... Quoting from the Jonathan Lewis article Richard posted.

    When talking about Balanced B-tree indexes, the term "balanced" means top to bottom, not left to right.
    Oracle really does implement a version of "Balanced B-tree indexes," so at any moment, all leaf blocks in an index are exactly the same distance from the root — a distance that can be found in the blevel column of the view user_indexes if the index has been recently analyzed, or as the height (which equals blevel + 1) in the view index_stats immediately after executing a validate index.
    Resist the argument that you need to rebuild Indexes regularly because "they become unbalanced." It isn't a valid argument.

    And these are the only facts. The rest is myth.

    --------------
    Sybrand Bakker
    Senior Oracle DBA
  • 22. Re: corrupted indexes
    108476 Journeyer
    Currently Being Moderated
    Hi Sybrand,
    When talking about Balanced B-tree indexes, the term "balanced" means top to bottom, not left to right.
    If I remember my data structures class from 30 years ago, b-trees follow preorder traversal, left to right:

    http://www.cs.usask.ca/content/resources/csconcepts/1998_6/bintree/2-2.html

    " pre order traversal prints the contents of a sorted tree, in pre order. In other words, the contents of the root node are printed first, followed by left subtree and finally the right subtree."
    Oracle really does implement a version of "Balanced B-tree indexes,"
    Yes, but with one critical exception - Oracle uses pbhysical data blocks as logical index nodes!
    Resist the argument that you need to rebuild Indexes regularly because "they become unbalanced."
    Wether you call it "fragmentatoin" or unbalanced", in many cases of high DML indexes (like SAP and Clintrial) index rebuilding needs to be done on a scheduled basis:

    https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/8750%3Fpage%3Dlast

    "What is happening here is the index is becoming unbalanced through constant inserts and deletes.

    Over time, the space the index occupies will continue to grow though there may be zero rows at the time the system starts and stops.

    Each deleted row continues to occupy space in the index segment until a rebuild is done.”
    It isn't a valid argument.
    I beg to differ. I support a SAP database with a key tables and index with 100 freelists defined, with 400 users doing nothing but adding and deleting rows.

    The performance gets so bad (for index FFS) that we have to rebuild them nightly!

    That's an extreme example, and feel free to call it "rare", but it happens a lot in large OLTP systems with thousands of online users . . .
  • 23. Re: corrupted indexes
    311441 Employee ACE
    Currently Being Moderated
    burleson wrote:

    Tell me Richard, is the Oracle segment advisor wrong when it identifies unbalanced indexes for rebuilding?

    http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2005-08-25.8847176467

    "The 10G segment advisor identifies segments that have become fragmented as a result of update and delete operations. Oracle describes these objects as being sparsely populated. Not only do sparsely populated objects waste space but they can also contribute to SQL performance problems. "

    Also, see right here, on OTN:

    http://www.oracle.com/technology/oramag/oracle/05-may/o35tuning.html
    No, I'm saying you're wrong. You, not the Oracle segment advisor.

    The Oracle segment advisor does not identify "unbalanced" indexes. In both the links you've refereneced, the word unbalanced is not mentioned once. Not once.

    Richard Foote
    http://richardfoote.wordpress.com/
  • 24. Re: corrupted indexes
    311441 Employee ACE
    Currently Being Moderated
    burleson wrote:

    I beg to differ. I support a SAP database with a key tables and index with 100 freelists defined, with 400 users doing nothing but adding and deleting rows.

    The performance gets so bad (for index FFS) that we have to rebuild them nightly!

    That's an extreme example, and feel free to call it "rare", but it happens a lot in large OLTP systems with thousands of online users . . .
    I support a SAP database that has approximately 4,500 users.

    Interestingly, we maintain our index structures in a manner such that we don't have to rebuild a single index.

    Not one.

    Richard Foote
    http://richardfoote.wordpress.com/
  • 25. Re: corrupted indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Richard Foote wrote:

    I support a SAP database that has approximately 4,500 users.

    Interestingly, we maintain our index structures in a manner such that we don't have to rebuild a single index.
    burleson wrote:I beg to differ. I support a SAP database with a key tables and index with 100 freelists defined, with 400 users doing nothing but adding and deleting rows.

    The performance gets so bad (for index FFS) that we have to rebuild them nightly!

    That's an extreme example, and feel free to call it "rare", but it happens a lot in large OLTP systems with thousands of online users . . .
    That's very convenient - lots of people run SAP so there's no question of giving away secret information by supplying a table name, or index name, or a few numbers about an object in the SAP schema. I'm sure the owners of the systems would give permission for such non-identifying information to be published.

    So could we ask Mr. Burleson to identify the index (table_name, index_name, index columns and order, freelist information, block size, etc.) and give us some numbers that show the index causes a performance problem unless rebuilt every night on his 400 user system.

    Then Mr. Foote can show us the same index definition and statistics from his system and explain what he's done to the same index so that he doesn't have to rebuild it every night on his 4,500 user system.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "Science is more than a body of knowledge; it is a way of thinking"
    Carl Sagan
  • 26. Re: corrupted indexes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Jonathan,

    Yes, a comparison between the two SAP databases (supported by Don and Richard respectively) -- for that index specifically and indexes generally -- would be useful !

    Index "size" (or explosion) can also depend on the nature of operations against the index and how the index was first set up. Your "Index Explosions" notes also indicate some cases when indexes may grow large.

    [This Peoplesoft discussion thread|http://tech.groups.yahoo.com/group/psftdba/message/2533] also has found issues with some indexes -- I've also seen indexes grow extraordinarily large, impacting not only Index Scans but, possibly, even DML against the table.

    [On asktom|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1396006300346456969#1563326300346627527], (towards the end of that thread) too, I presented a similar issue. A rebuild or coalesce returns index size and performance of batch jobs to "normal".


    However, I am not talking of indexes being "unbalanced". But, rather, that, due to some peculiarities, (e.g concurrent delete+insert without intervening commit, also with new key value for each batch of rows) some indexes do need a periodic rebuild or coalesce (to "shrink" them, not to "re-balance" them).

    Don needs to identify why his index does need a daily rebuild. His performance gains probably come from the index shrinking at the beginning of the day (not from a perceived "re-balancing").



    Hemant K Chitale
    http://hemantoracledba.blogspot.com
  • 27. Re: corrupted indexes
    311441 Employee ACE
    Currently Being Moderated
    Hi Jonathan

    Mr Foote thinks it's a great idea :)

    I would love to know exactly why such an index needs to be rebuilt every day in SAP. Once I have the specific details (including this Fast Full Index Scan query), more than happy to compare differences.

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 28. Re: corrupted indexes
    108476 Journeyer
    Currently Being Moderated
    I'm saying you're wrong.
    No, you are just playing "word games".

    I have a real hard time believing that you don't understand the fundamental nature of Oracle indexes on the data blocks.

    You do understand; you just want to argue about proper word usage.

    FACT - After a large delete, Oracle will re-link an index data block back onto the freelists, where the block might get index entries from other places in the tree.

    FACT - When massive deletes occur, Oracle DOES NOT re-balance the index nodes . . .
    the word unbalanced is not mentioned once.
    You can call it "bad", "exploded", unbalanced, fragmented, call it whatever you like . . .

    As to the SAP issues, there are several OSS notes specifically related to rebuilding of Oracle indexes, especially when they are defined with multiple freelist groups.

    https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/8750%3Fpage%3Dlast

    +"For index quality, we should talk about fragmentation, or balancing, which relate to how much extra space is in the index, leading to longer access times, as well as the side effect of pushing good data out of our caches such as the DB_BLOCK_BUFFER (DB_CACHE in recent Oracle versions)."+

    Also, don't forget that SAPDBA has a utility specifically designed to identify "bad" Oracle indexes for rebuilding.

    http://www.sap-basis-abap.com/bc/rebuilding-indexes-in-sap.htm

    As to specific indexes, VBAP___0 comes to mind . . .
  • 29. Re: corrupted indexes
    635471 Expert
    Currently Being Moderated
    burleson wrote:
    You can call it "bad", "exploded", unbalanced, fragmented, call it whatever you like . . .
    A 1:1 relationship between words and meanings in this technical context would be my own preference. Everyone knows that m:m relationships cause problems, surely.

    I wouldn't be taking database terminology lessons from SAP.
1 2 3 4 7 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points