On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,524 Users
  • 2,269,754 Discussions
  • 7,916,770 Comments

Discussions

corrupted indexes

1356710

Answers

  • 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
  • 108476
    108476 Member Posts: 2,184
    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 . . .
  • 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/
  • 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/
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,142 Blue Diamond
    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
  • Hemant K Chitale
    Hemant K Chitale Oracle DBA OCP OCE SingaporeMember Posts: 15,759 Blue Diamond
    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
  • Richard Foote
    Richard Foote Member Posts: 483
    edited Sep 11, 2009 4:52AM
    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/
  • 108476
    108476 Member Posts: 2,184
    edited Sep 11, 2009 5:55AM
    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 . . .
  • 635471
    635471 Member Posts: 1,937
    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.
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,142 Blue Diamond
    Hemant K Chitale wrote:

    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|http://jonathanlewis.wordpress.com/2009/07/28/index-explosion/]* notes also indicate some cases when indexes may grow large.
    Not so much "large" as "two or three times larger than expected" - the problem is bounded, not constantly increasing
    [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.
    I would treat that example with a little caution, and not one to quote as dependable evidence of a real problem, for two reasons:

    <ul>
    first - the originator makes a comment about "indexes not re-using space unless the exact value is re-inserted", thus demonstrating a basic misunderstanding of Oracle's implementation. Based on that, I have to assume that he may have failed to investigate the problem properly, and may not have understood any evidence he observed.

    secondly - when David Kurtz (one of the world's specialists in trouble-shooting Peoplesoft) asked him if he has made sure the index was not a bitmap index, he gave no reply. The faiure to respond (and, indeed, David's feeling that it was a necessary question) make me wonder if the issue was something very basic that the originator had not realised was relevant.
    </ul>
    [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".
    A couple of points about your Peoplesoft example:
    <ul>
    a) The table has 10 columns
    b) Both indexes are built on the same 9 out of 10, and both start with the same four in the same order
    c) The concurrent processes insert data with "process_id = {my_id}" but start by deleting data for all process_ids.
    </ul>

    This does not look like a mechanism designed by someone who understands Oracle, or indexing, let alone Oracle's indexing (or even concurrency).

    I can think of a couple of reasons (including hypothetical bugs / edge cases) why the Peoplesoft code might result in index space getting lost - particularly space that should be put onto freelists but doesn't get there, or doesn't get re-used. I've already built a test case emulating this behaviour by applying an unsuitable (but not insanely stupid) design error.

    If you care to extract the definitions of the objects using dbms_metadata.get_ddl() and post them here, I'll spend a little time seeing if I can make the problem appear with your object definitions.

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

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
This discussion has been closed.