This discussion is archived
1 2 3 4 11 Previous Next 163 Replies Latest reply: Oct 22, 2009 3:23 PM by Hoek Go to original post RSS
  • 15. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Hi Jonathan,
    so does 50 DMLs per second mean a clerk can enter an order in 20 seconds ... what a tedious job
    Whatever. Like I said, I don't recall the exact numbers, so please put down your calculator!

    It's a very busy, high concurrency system, that is my point!


    *************************************************************************
    It's not surprising that deleting a large volume of old data requires a coalesce if it's the typical sort of orders table with meaningless key
    If by "meaningless key", you mean a sequence, then yes!
    A methods also known as "trial and error" - not necessarily a bad thing, of course.
    I cannot imagine any other way to eat this elephant, can you?

    I know that you are a math whiz, but this would be extremely hard to model with equations . . .

    *************************************************************************
    But do you have any reasonably argument about why a longer running test with a few more sessions but a lower concurrency level and a lower rate of inserts should behave any differently from the original test ?
    Yeah. When I took simulation in college they taught us that in order to be generalizable to the real-world, a simulation had to replicate the real world.

    Modeling index fragmentation with only a single user is not very realistic, don't you agree?

    Specifically, I've noted that "weirdness" happens when you have boatloads of end-users competing for 200 freelists against five objects.

    Try it, you might be surprised . . .


    *************************************************************************
    Not a problem - even my laptop could cope with that quite easily.
    Do you know how to model it without using specialized benchmarking software?

    *************************************************************************
    Of course we've also made it clear that most index rebuild operations are just a waste of resources.
    Sorry, how can you come to that conclusion from the evidence that you have cited?

    Your assertion also assumes that "most" DBA's are inept, don't you think?

    Now if you were to say "most index rebuild operations performed by inept posers are just a waste of resources", I would agree.
  • 16. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:
    Hi Jonathan,
    so does 50 DMLs per second mean a clerk can enter an order in 20 seconds ... what a tedious job
    Whatever. Like I said, I don't recall the exact numbers, so please put down your calculator!
    It's a very busy, high concurrency system, that is my point!
    Personally I wouldn't call 50 DMLs per second a very busy, high concurrency system.

    >
    A methods also known as "trial and error" - not necessarily a bad thing, of course.
    I cannot imagine any other way to eat this elephant, can you?
    I like to start by understanding the technology, and estimating the likely concurrency threat.
    That way it's possible to avoid the silliest mistakes. But starting from a reasonable estimate
    I think I'd still go for trial and error to refine it - but if trial and error started moving me too
    far from my estimate I'd stop and rethink the problem.
    *************************************************************************
    But do you have any reasonably argument about why a longer running test with a few more sessions but a lower concurrency level and a lower rate of inserts should behave any differently from the original test ?
    Yeah. When I took simulation in college they taught us that in order to be generalizable to the real-world, a simulation had to replicate the real world.
    But that doesn't answer the question.

    You suggested that instead of running a test with N concurrent users executing X statements per second I should change this to M concurrent users executing Y statements per second. My original values for N and X put more stress on the critical components of the model than your suggestions for M and Y.

    You have previously insisted that the problems appear with "super-high" DML, and "high concurrency" systems. But now you're telling me that I didn't see a problem because my level of concurrency was too high.

    Modeling index fragmentation with only a single user is not very realistic, don't you agree?
    it would be perfectly realistic if a client insisted that they are seeing index fragmentation on a single user system; however. since you've referenced my postings about index explosions, I assume you've noticed that I wasn't running a single user test, it was a multi-user test that could be adjusted very easily to run with a an arbitrarily high level of users and actual concurrency.
    Specifically, I've noted that "weirdness" happens when you have boatloads of end-users competing for 200 freelists against five objects.

    Try it, you might be surprised . . .
    I might be, but I probably wouldn't be - but before I waste the effort, what would you say if I run up the model and show that nothing surprising and nasty happens to the indexes ?


    >
    *************************************************************************
    Not a problem - even my laptop could cope with that quite easily.
    Do you know how to model it without using specialized benchmarking software?
    Yes - just read the description you've given us, it's not a difficult one to model.


    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 17. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:

    It would be really interesting if you could redo your "index explosion" tests with a real-world condition, simulating 20 updates per second for eight hours.

    Maybe then, it will become clear why some high-impact indexes require scheduled rebuilding/coalescing . . .
    Let me repeat the questions I asked above

    <ul>
    Why would you find it interesting ?
    Do you want to make any prediction about what will happen ?
    How does inserting data from multiple concurrent session into an indexed table not feature in the real world ?
    </ul>

    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
  • 18. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Why would you find it interesting ?
    To see you finally "get it", and understand something that working DBA's know to be self-evident:

    http://www.dba-oracle.com/t_scheduling_oracle_index_rebuilding.htm

    It's quite true that rebuilding indexes does not always help, but there are many times when rebuilding an index does help performance, especially for indexes that experience high DML activity and have multiple freelists and experience index range scans or index fast-full scans. Because many tasks compete from many freelists to get blocks as the index grows, the physical structure of the index data blocks becomes scrambled.

    In an index with a single freelist, index range scans need only perform a single movement of the read-write head on disk, and the contiguous data locks can be read as fast as the disk can spin. In a large multi-user environment, index blocks become discontiguous and the benefits of multiblock reads for index range scans is lost.
    Do you want to make any prediction about what will happen ?
    Let me be as clear as I can:

    During scheduled maintanance  index rebuilding is a cost-free activity and therefore, the amount of the benefit is incidental.

    I'm also going to go farther and recommend scheduled index rebuilding as a DBA best practice.

    I think that this advice that "index rebuilding is a waste of time" is dangerous for several reasons:

    - It robs the DBA of a proven effective tuning tool - Even if only 30% of the Oracle indexes benefit from a scheduled index rebuild, then it's still worth doing.*

    - It puts a DBA at-risk - If a DBA managaer caught a DBA at work, trying to justify rebuild an index (a zero-cost activity) they could get fired. I would personally recommend firing any DBA who wasted time doing this during company time. Of course, it's perfectly OK to explore this on your own time.

    Let me save you the trouble, since we have had this argument at least three times before!

    1 - This is where you note that index rebuilding is not cost-free, that it's expensive and risky.

    2 - Then, I reply that it's only costly for untrained, inept or reckless DBA's . . .

    But here is where we agree:

    If I were advising an untrained, inept or reckless DBA I would give the same advice that you do, don't rebuild indexes because the risk outweighs the benefits.

    Maybe the real answer is to "block" advanced information from these types of people:

    - I once was called in to a shop with severe DML performance problems. The DBA was grossly untrained and had read one of your DBAZine articles on indexing. Because he was not too smart, all he took from your article was that "indexes are good" and started building indexes on every column of every tables. Then he learned about multi-column indexes and things got real ugly. He was fired, and he blamed your article for getting him fired!

    - A fellow in the Netherlands (you know who I mean) once accused me of nearly costing him his job! He ran one of my scripts without knowing what it was doing, not understanding that it's not a good idea to do a validate index structure on his live production database! Of course, he blamed his own ineptitude on my script!

    You were a college professor like me, right? You know the old saying: "When I get an "A" it's because I'm smart. When I gat a "C" it's because you are a bad professor".

    What are we supposed to do, put "idiot" warnings on all of our pages?

    You see, when people make statements like "index rebuilding is a waste of time", it leaves working DBA's scratching their heads, wondering why they don't "get it".

    Please Jonathan, try to "get it". . . .

    I don't expect you to agree, but at least understand why over-generalized rules of thumb like this are a bad thing . . .
  • 19. Re: Index rebuild
    635471 Expert
    Currently Being Moderated
    burleson wrote:
    ...
    I'm also going to go farther and recommend scheduled index rebuilding as a DBA best practice.
    ...
    If I were advising an untrained, inept or reckless DBA I would give the same advice that you do, don't rebuild indexes because the risk outweighs the benefits.
    ...
    - I once was called in to a shop with severe DML performance problems. The DBA was grossly untrained and had read one of your DBAZine articles on indexing. Because he was not too smart, all he took from your article was that "indexes are good" and started building indexes on every column of every tables. Then he learned about multi-column indexes and things got real ugly. He was fired, and he blamed your article for getting him fired!

    - A fellow in the Netherlands (you know who I mean) once accused me of nearly costing him his job! He ran one of my scripts without knowing what it was doing, not understanding that it's not a good idea to do a validate index structure on his live production database! Of course, he blamed his own ineptitude on my script!
    It seems that intelligent DBA's, who understand both how index structures work (thanks to Jonathan and Richard) and their data and application, could decide for themselves on the pros and cons of index rebuilds and do not need your "best practice" advice. Doesn't that means that your advice will only be considered by the untrained, inept or reckless? It seems that they would be better served by best practice advice to read Richard and Jonathan's articles.
  • 20. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:
    Why would you find it interesting ?
    To see you finally "get it", and understand something that working DBA's know to be self-evident:

    http://www.dba-oracle.com/t_scheduling_oracle_index_rebuilding.htm

    It's quite true that rebuilding indexes does not always help, but there are many times when rebuilding an index does help performance, especially for indexes that experience high DML activity and have multiple freelists and experience index range scans or index fast-full scans. Because many tasks compete from many freelists to get blocks as the index grows, the physical structure of the index data blocks becomes scrambled.

    In an index with a single freelist, index range scans need only perform a single movement of the read-write head on disk, and the contiguous data locks can be read as fast as the disk can spin. In a large multi-user environment, index blocks become discontiguous and the benefits of multiblock reads for index range scans is lost.
    Do you want to make any prediction about what will happen ?
    Let me be as clear as I can:

    During scheduled maintanance  index rebuilding is a cost-free activity and therefore, the amount of the benefit is incidental.

    I'm also going to go farther and recommend scheduled index rebuilding as a DBA best practice.

    I think that this advice that "index rebuilding is a waste of time" is dangerous for several reasons:

    - It robs the DBA of a proven effective tuning tool - Even if only 30% of the Oracle indexes benefit from a scheduled index rebuild, then it's still worth doing.*

    - It puts a DBA at-risk - If a DBA managaer caught a DBA at work, trying to justify rebuild an index (a zero-cost activity) they could get fired. I would personally recommend firing any DBA who wasted time doing this during company time. Of course, it's perfectly OK to explore this on your own time.

    Let me save you the trouble, since we have had this argument at least three times before!

    1 - This is where you note that index rebuilding is not cost-free, that it's expensive and risky.

    2 - Then, I reply that it's only costly for untrained, inept or reckless DBA's . . .

    But here is where we agree:

    If I were advising an untrained, inept or reckless DBA I would give the same advice that you do, don't rebuild indexes because the risk outweighs the benefits.

    Maybe the real answer is to "block" advanced information from these types of people:

    - I once was called in to a shop with severe DML performance problems. The DBA was grossly untrained and had read one of your DBAZine articles on indexing. Because he was not too smart, all he took from your article was that "indexes are good" and started building indexes on every column of every tables. Then he learned about multi-column indexes and things got real ugly. He was fired, and he blamed your article for getting him fired!

    - A fellow in the Netherlands (you know who I mean) once accused me of nearly costing him his job! He ran one of my scripts without knowing what it was doing, not understanding that it's not a good idea to do a validate index structure on his live production database! Of course, he blamed his own ineptitude on my script!

    You were a college professor like me, right? You know the old saying: "When I get an "A" it's because I'm smart. When I gat a "C" it's because you are a bad professor".

    What are we supposed to do, put "idiot" warnings on all of our pages?

    You see, when people make statements like "index rebuilding is a waste of time", it leaves working DBA's scratching their heads, wondering why they don't "get it".

    Please Jonathan, try to "get it". . . .

    I don't expect you to agree, but at least understand why over-generalized rules of thumb like this are a bad thing . . .
    Dear Mr. Burleson,

    Please try to answer the question and avoid the hand-waving, irrrelevant comments, and ad hominen remarks.

    >
    To see you finally "get it", and understand something that working DBA's know to be self-evident:
    If you think that running the test with 20 concurrent processes inserting data for eight hours will make me "get it" you should be able to explain what will happen to the index that will allow me to "get it" and tell me what evidence will suddenly become visible that helps me to "get it".

    Presumably you can tell me how you would examine the index to demonstrate that something had happened to it that needed fixing.

    Regards
    Jonathan Lewis
  • 21. Re: Index rebuild
    706417 Explorer
    Currently Being Moderated
    Hi Don & Jonathan,

    I couldn't resist a little jest:
    The Two Great Experts

    Love - Don Lewis
  • 22. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Dear Mr. Burleson,

    You suggested in [+*an earlier posting in this thread*+|http://forums.oracle.com/forums/message.jspa?messageID=3789564#3789564] that I would see why some indexes need rebuilding if I ran my test case with 20 concurrent processes for eight hours. So far though, despite [+*being asked twice*+|http://forums.oracle.com/forums/message.jspa?messageID=3797835#3797835] for [+*some technical justification*+|http://forums.oracle.com/forums/message.jspa?messageID=3789799#3789799] for this claim, you have declined supply any predictions about the likely state of the index at the end of the test.

    I am aware of various circumstances that make it beneficial to coalesce, or even rebuild, an index; however I also know how to configure indexes to handle highly concurrent activity without needing any maintenance activity.

    Since you seem to think that the index in my test case would obviously need rebuilding at the end of an eight hour run, would you please tell me what evidence you would use to support that assumption ?

    (Update 9th Oct: Still hoping for an answer.)

    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 23. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Hi Jonathan,
    you have declined supply any predictions
    I can predict with 100% confidence that you have the skill to adjust background settings to make your test case support whatever conclusion you desire!

    Guaranteed!

    I'm not going over the "rigged" test case issue again, we have already had that discussion. . . .
    I am aware of various circumstances that make it beneficial to coalesce, or even rebuild, an index;
    Perfect!

    Sadly, some people, instead of investigating this phenomenon, weant to "prove" that it's false, or argue about whether it is “infrequent” or “rare”, and if so, to what degree.

    That’s NOT helpful.

    Because it’s not the default case, and goofball can write an invalid test case and say “see, rebuilding indexes is a waste of time”.

    That not only bad science, it misses the point.

    Now, what would a real scientist do?

    They would focus on cases where it does happen, and try to create a generalizable set of rules to describe it . . .

    Jonathan, how about stepping up to the plate and give us the rules for detecting cases WHEN an indexes will benefit from rebuilding?*
    Still hoping for an answer.
    If you want your research to have value for working DBA’s, give us a takeaway, something that explain “When” a phenomenon happens and “How” to detect it.

    Jonathan, a real scientist would try to give us a script to be able to detect the conditions that you describe, when an index benefits from rebuilding.

    Me, I’m still hoping that you step up to the challenge,

    Anybody who truly understand Oracle knows that criticizing the work of others with useless “negative” proofs is invalid. . . .
    supply any predictions
    - I predict that you have the skill to isolate the variables involved in this phenomenon of indexes that benefit from scheduled rebuilding (PCTFREE ASSM, avg_rows_len, block size etc.)

    - I predict that you have the math skills to qualitatively describe these situations

    - I predict that you have the skill to write a dictionary query to help DBA’s find these indexes

    Please, Jonathan, show us your chops . . .
  • 24. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:
    Hi Jonathan,
    you have declined supply any predictions
    I can predict with 100% confidence that you have the skill to adjust background settings to make your test case support whatever conclusion you desire!

    Guaranteed!

    I'm not going over the "rigged" test case issue again, we have already had that discussion. . . .
    I am aware of various circumstances that make it beneficial to coalesce, or even rebuild, an index;
    Perfect!

    Sadly, some people, instead of investigating this phenomenon, weant to "prove" that it's false, or argue about whether it is “infrequent” or “rare”, and if so, to what degree.

    That’s NOT helpful.

    Because it’s not the default case, and goofball can write an invalid test case and say “see, rebuilding indexes is a waste of time”.

    That not only bad science, it misses the point.

    Now, what would a real scientist do?

    They would focus on cases where it does happen, and try to create a generalizable set of rules to describe it . . .

    Jonathan, how about stepping up to the plate and give us the rules for detecting cases WHEN an indexes will benefit from rebuilding?*
    Still hoping for an answer.
    If you want your research to have value for working DBA’s, give us a takeaway, something that explain “When” a phenomenon happens and “How” to detect it.

    Jonathan, a real scientist would try to give us a script to be able to detect the conditions that you describe, when an index benefits from rebuilding.

    Me, I’m still hoping that you step up to the challenge,

    Anybody who truly understand Oracle knows that criticizing the work of others with useless “negative” proofs is invalid. . . .
    supply any predictions
    - I predict that you have the skill to isolate the variables involved in this phenomenon of indexes that benefit from scheduled rebuilding (PCTFREE ASSM, avg_rows_len, block size etc.)

    - I predict that you have the math skills to qualitatively describe these situations

    - I predict that you have the skill to write a dictionary query to help DBA’s find these indexes

    Please, Jonathan, show us your chops . . .
    Dear Mr. Burleson,

    Once again you have chosen to pour out a stream of irrelevant comments rather than answering a simple technical question about indexes.

    I have published code to demonstrate the type of activity that can cause an index to become larger than it needs to be; I have described why this happens, and explained how to address the issue so that it doesn't happen.

    You have seen the material I published and claimed that if I ran the test code at a rate of 20 concurrent inserts per second I would understand why some indexes need to be rebuilt.

    There are various, well-documented, reasons why some indexes do need to be rebuilt (though a coalesce+ is more often the appropriate strategy), but there is no reason why the index in my test case should run into any such problems - especially at the relatively low rate of activity that you suggested.

    Since you claimed that there would be some evidence for rebuilding at the end of the test run, I asked you to describe what that evidence would be; but you have repeatedly declined to say anything of any practical value about the state of the index.

    We can only assume that you don't know anything about the likely state of the index, don't know how to identify an index that is in need of rebuilding and, presumably have been rebuilding some indexes repetitively in the past without realising that there might have been a simple configuration error that should have been addressed instead.


    Regards
    Jonathan Lewis

    +"If you claim that something is true, I will examine the evidence which supports your claim;+
    +if you have no evidence, I will not accept that what you say is true and I will think you a foolish+
    +and gullible person for believing it so"+
    Richard Dawkins
  • 25. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Hi Jonathan,
    rather than answering a simple technical question about indexes.
    It wasn't a question, pay attention.

    You asked me to make a prediction, and I answered this already:

    +"In an index with a single freelist, index range scans need only perform a single movement of the read-write head on disk, and the contiguous data locks can be read as fast as the disk can spin. In a large multi-user environment, index blocks become discontiguous and the benefits of multiblock reads for index range scans is lost."+
    I have published code to demonstrate the type of activity that can cause an index to become larger than it needs to be
    Oh please. Your talent for biasing test case "proofs" is legendary.

    All that you have "proven" is that artificial test cases are less than useless because you can manipulate them at-will to support whatever conslusions you want.

    We both know that "test cases" prove NOTHING. They are just a tool that can be abused to mislead the public and to unfairly discredit honest experts . . .
    There are various, well-documented, reasons why some indexes do need to be rebuilt
    Is that so!

    What are they, prey tell?

    Give us an executive summary. . . .

    I'll predict that you can't do it . . . .
    We can only assume that you don't know anything about the likely state of the index
    Again, a conclusion that is not supported by any evidence whatsoever.

    The real reason is that I'm wise to your biased test case trick!

    You willfully manipulate your "test cases", rigging them to "prove" whatever you want, and I'm not taking the bait again.
    Once again you have chosen to pour out a stream of irrelevant comments
    Scientific investigation is NOT irrelavant!

    SHOW US THE EVIDENCE, JONATHAN . . . .
    if you have no evidence, I will not accept that what you say is true and I will think you a foolish and gullible person for believing it so
    You claim to understand this issue, yet you refuse to provide any evidence.  Simply provide a script to detect indexes that require rebuilding.

    This reminds me of when you rated Rich Niemiec's book as "poor", without citing a shred of evidence, nor mentioning the clear conflict of interest, because you are the author of a competing book:

    http://www.amazon.co.uk/Oracle-Database-Performance-Techniques-Osborne/dp/0072263059

    You wll never be able to coat-tail your way into becoming an Oracle expert simply by hurling insults at those who are.

    Write all the test cases you want, there is no substitute for experience . . .
    and, presumably have been rebuilding some indexes repetitively in the past without realising that there might have been a simple configuration error that should have been addressed instead.
    Got any evidence? Of course not!
    Richard Dawkins
    I don't believe in Richard Dawkins.

    Richard Dawkins is just a figment of your imagination, a dangerous delusion . . .
  • 26. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:
    Hi Jonathan,
    rather than answering a simple technical question about indexes.
    It wasn't a question, pay attention.

    You asked me to make a prediction, and I answered this already:

    +"In an index with a single freelist, index range scans need only perform a single movement of the read-write head on disk, and the contiguous data locks can be read as fast as the disk can spin. In a large multi-user environment, index blocks become discontiguous and the benefits of multiblock reads for index range scans is lost."+
    I have published code to demonstrate the type of activity that can cause an index to become larger than it needs to be
    Oh please. Your talent for biasing test case "proofs" is legendary.

    All that you have "proven" is that artificial test cases are less than useless because you can manipulate them at-will to support whatever conslusions you want.

    We both know that "test cases" prove NOTHING. They are just a tool that can be abused to mislead the public and to unfairly discredit honest experts . . .
    There are various, well-documented, reasons why some indexes do need to be rebuilt
    Is that so!

    What are they, prey tell?

    Give us an executive summary. . . .

    I'll predict that you can't do it . . . .
    We can only assume that you don't know anything about the likely state of the index
    Again, a conclusion that is not supported by any evidence whatsoever.

    The real reason is that I'm wise to your biased test case trick!

    You willfully manipulate your "test cases", rigging them to "prove" whatever you want, and I'm not taking the bait again.
    Once again you have chosen to pour out a stream of irrelevant comments
    Scientific investigation is NOT irrelavant!

    SHOW US THE EVIDENCE, JONATHAN . . . .
    if you have no evidence, I will not accept that what you say is true and I will think you a foolish and gullible person for believing it so
    You claim to understand this issue, yet you refuse to provide any evidence.  Simply provide a script to detect indexes that require rebuilding.

    This reminds me of when you rated Rich Niemiec's book as "poor", without citing a shred of evidence, nor mentioning the clear conflict of interest, because you are the author of a competing book:

    http://www.amazon.co.uk/Oracle-Database-Performance-Techniques-Osborne/dp/0072263059

    You wll never be able to coat-tail your way into becoming an Oracle expert simply by hurling insults at those who are.

    Write all the test cases you want, there is no substitute for experience . . .
    and, presumably have been rebuilding some indexes repetitively in the past without realising that there might have been a simple configuration error that should have been addressed instead.
    Got any evidence? Of course not!
    Richard Dawkins
    I don't believe in Richard Dawkins.

    Richard Dawkins is just a figment of your imagination, a dangerous delusion . . .
    Dear Mr. Burleson,

    My error - since I posed my questions in response to your suggestion that I ought to run my test simulating 20 updates per second for eight hours, I didn't realise that your statement (following) was supposed to be a prediction about the results of the test. It seemed to be a simple observation quoted from one of your websites.
    +"In an index with a single freelist, index range scans need only perform a single movement of the read-write head on disk, and the contiguous data locks can be read as fast as the disk can spin. In a large multi-user environment, index blocks become discontiguous and the benefits of multiblock reads for index range scans is lost."+
    A few points to note, however:

    Even when you have an index with a single freelist you don't, in general, end up with logically adjacent leaf blocks being physically adjacent - so a range scan that crosses two leaf blocks would not necessarily read two consecutive blocks from the same extent. (We'll assume, for the sake of simplicity, that two consecutive blocks in a single extent are automatically adjacent on disc - but even that isn't necessarily true). Remember: in many indexes the data insertion is scattered randomly across the entire width of the index, so leaf block splits can occur anywhere in the index, which means consecutive blocks taken off a freelist could be inserted anywhere into the index structure. It is only in sequence-based or time-based indexes that a single freelist is likely to offer you a significant +"adjacency pattern"+.


    When a session does a "large index range scan", it will read one leaf block into the buffer cache, then read a table block. If it's a very simple query against a single table it will then go back and forth between the index and the table several times - revisiting the same index leaf block in memory but typically visiting (and reading from disc) several different table blocks. In a "large multi-user system" the disk heads will probably have moved before the session wants to read the next index leaf block from disc.

    For large index range scans in the final table of a multi-table join, recent versions of Oracle have been able to perform multi-block reads - "db file parallel reads" when the required blocks are not adjacent and "db file scattered reads" when they are. This means that there are, indeed, circumstances where a very large index range scan could be a little faster after rebuilding an index because of the adjacency effect you describe - but in a "large multi-user" system there are trouble-shooting questions that should be asked about that type of query activity before you even think about rebuilding indexes regularly: if the queries are frequent why isn't the data access more precise, if the queries are frequent why is the index not getting cached, and so on. (I note, by the way, that your article seems to assume you have plenty of downtime every weekend to rebuild indexes - I have pointed out in the past that if that's the case you probably won't do much damage by doing so; but most of the large multi-user systems I see these days don't stop for the weekend.)

    When you define multiple freelists on indexes, you typically need a much lower number of freelists than you need on the table. This is stems from two considerations: first, index entries tend to be smaller than table rows, so the rate at which index leaf blocks fill is slower than the rate at which table blocks fill, so the rate at which new blocks are needed for an index is slower than the rate at which new table blocks are needed; secondly, because index entries are typically scattered randomly across the index the probability of simultaneous leaf block splits is much lower than the probability of simultaneous requirements for new table blocks.


    Now - to address your "prediction":
    When I ran the test case with 20 concurrent processes totalling 20 inserts per second and only one freelist defined on the table and index, the +"adjacency"+ pattern was visible because the index was a time-based index.

    When I ran the test case at 50 concurrent concurrent processes totalling 50 inserts per second, with 4 freelist groups and 50 freelists the +"adjacency"+ pattern disappeared. But that was hardly a surprise, nor a reason to make me decide that the index needed rebuilding. The resulting indexes were less efficiently packed than one would hope for on a production system - some of the damage was due to an unnecessarily high setting for freelists, some was the obvious side effect of the high level of CPU utilisation on a small machine.


    A little item, just for information: if you create a table with four freelist groups+, then you need to ensure that the number of freelists+ is an odd number or you won't be using all the freelists. In your example with freelist groups 4, freelists 50+, Oracle would have been using the 'odd-numbered' process freelists in the 'odd-numbered' freelist group blocks and the 'even-numbered' process freelists in the 'even-numbered' freelist group blocks. In effect you were running as if you had declared freelists 25. When you set freelists and freelists groups, the two numbers should have no common factors. *(Updated 19th Oct - this is only true if you use multiple freelist groups in single-instance Oracle)*

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


    +"We have, indeed, developed evidence for such exceptions ourselves,+
    +because a theory should be challenged by those who have postulated it."+
    J.L.King & T.H.Jukes

    Edited by: Jonathan Lewis on Oct 11, 2009 4:05 AM

    Edited by: Jonathan Lewis on Oct 19, 2009 6:23 PM
  • 27. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Hi Jonathan,
    It is only in sequence-based or time-based indexes that a single freelist is likely to offer you a significant "adjacency pattern".
    Exactly! That's why "Oracle Scientists" can't easily replicate the issue.

    You claim that you have written a valid benchmark that simulates the acts of 50 concurrent Oracle users, right?

    If it's truly representative, how about publishing it in http://www.tpc.org?

    But remember, you have only succeeded in finding one isloated case where indexes required scheduled rebuilding, there are lots more cases!

    A scientist starts by observing the real-world and describing it, which you have done, albeit for a single isolated case.

    You are not yet finished because you have not generalized your findings yet!

    You need to describe the functional dependency between all of the salient variables if you want to solve this problem.

    - What does the relationship between the index key avg_row_len and index blocksize factor into index rebuilding?

    - Why do ASSM indexes required scheduled index rebuilds more than "manual freelists"?

    - What is the relationship between the DML delete workload and this "too large" index that you have witnessed?



    ***********************************************************************

    I'll ask again.

    You claim to understand the fundamental nature of when indexes need to be rebuilt, and you claim to have adequately described it.

    I'm no so sure. . .

    Can we see your guidelines, please?

    I don't understand why you steadfastly claim to have solved the issue, yet refuse to give us a script to detect indexes that need rebuilding?

    If you have not been able to generalize the cases where indexes require rebuilding, just say so, it's not a big deal.

    At least you have finally recognized it, and that only took ten years . . .

    You may be getting discouraged, but fear not, you ARE making progress, glacially slow progress, but progress nonetheless . . .

    *************************************************************************
    if you create a table with four freelist groups, then you need to ensure that the number of freelists is an odd number or you won't be using all the freelists.
    Not according to Oracle Technical support!

    I actually discussed this issue with Rich Niemiec, (in the context of using freelist groups in non-RAC systems) and he never mentioned anything about even-odd freelists when using freelist groups. Not once.

    If Rich had said this, I would take his word for it, but since your experience and credientials are completely unknown and undocumented, I hope you will understand my request to see some documented evidence, issued by Oracle Corporation.

    ************************************************************************
    Richard Dawkins,
    Do you really think that quoting one of the most hated idiots in the world will help your argument?

    Quoting Richard Dawkins is akin to quoting Adolph Hitler . . . .

    See for yourself:

    Tony Blair equates atheists with violent religious extremists in Georgetown speech:

    http://www.examiner.com/examiner/x-4275-Secularism-Examiner~y2009m10d11-Tony-Blair-equates-atheists-with-violent-religious-extremists-in-Georgetown-speech

    George H.W. Bush Said Atheists Shouldn’t Be Considered U.S. Citizens or Patriots:

    http://www.positiveatheism.org/writ/ghwbush.htm
  • 28. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:
    Hi Jonathan,
    It is only in sequence-based or time-based indexes that a single freelist is likely to offer you a significant "adjacency pattern".
    Exactly! That's why "Oracle Scientists" can't easily replicate the issue.

    You claim that you have written a valid benchmark that simulates the acts of 50 concurrent Oracle users, right?

    If it's truly representative, how about publishing it in http://www.tpc.org?

    But remember, you have only succeeded in finding one isloated case where indexes required scheduled rebuilding, there are lots more cases!

    A scientist starts by observing the real-world and describing it, which you have done, albeit for a single isolated case.

    You are not yet finished because you have not generalized your findings yet!

    You need to describe the functional dependency between all of the salient variables if you want to solve this problem.

    - What does the relationship between the index key avg_row_len and index blocksize factor into index rebuilding?

    - Why do ASSM indexes required scheduled index rebuilds more than "manual freelists"?

    - What is the relationship between the DML delete workload and this "too large" index that you have witnessed?



    ***********************************************************************

    I'll ask again.

    You claim to understand the fundamental nature of when indexes need to be rebuilt, and you claim to have adequately described it.

    I'm no so sure. . .

    Can we see your guidelines, please?

    I don't understand why you steadfastly claim to have solved the issue, yet refuse to give us a script to detect indexes that need rebuilding?

    If you have not been able to generalize the cases where indexes require rebuilding, just say so, it's not a big deal.

    At least you have finally recognized it, and that only took ten years . . .

    You may be getting discouraged, but fear not, you ARE making progress, glacially slow progress, but progress nonetheless . . .

    *************************************************************************
    if you create a table with four freelist groups, then you need to ensure that the number of freelists is an odd number or you won't be using all the freelists.
    Not according to Oracle Technical support!

    I actually discussed this issue with Rich Niemiec, (in the context of using freelist groups in non-RAC systems) and he never mentioned anything about even-odd freelists when using freelist groups. Not once.

    If Rich had said this, I would take his word for it, but since your experience and credientials are completely unknown and undocumented, I hope you will understand my request to see some documented evidence, issued by Oracle Corporation.

    ************************************************************************
    Richard Dawkins,
    Do you really think that quoting one of the most hated idiots in the world will help your argument?

    Quoting Richard Dawkins is akin to quoting Adolph Hitler . . . .

    See for yourself:

    Tony Blair equates atheists with violent religious extremists in Georgetown speech:

    http://www.examiner.com/examiner/x-4275-Secularism-Examiner~y2009m10d11-Tony-Blair-equates-atheists-with-violent-religious-extremists-in-Georgetown-speech

    George H.W. Bush Said Atheists Shouldn’t Be Considered U.S. Citizens or Patriots:

    http://www.positiveatheism.org/writ/ghwbush.htm
    Dear Mr. Burleson,

    I fear your comments are becoming increasingly irrelevant, inane and inventive. Your concept of the scientific approach is also somewhat exotic.
    I will, however, address the only item that could vaguely be called a rational follow-up to my earlier technical comments.

    >
    if you create a table with four freelist groups, then you need to ensure that the number of freelists is an odd number or you won't be using all the freelists.
    Not according to Oracle Technical support!

    I actually discussed this issue with Rich Niemiec, (in the context of using freelist groups in non-RAC systems) and he never mentioned anything about even-odd freelists when using freelist groups. Not once.

    If Rich had said this, I would take his word for it, but since your experience and credientials are completely unknown and undocumented, I hope you will understand my request to see some documented evidence, issued by Oracle Corporation.
    In an earlier post you said that you had a script to "monitor the fragmentation and loading of the individual freelists" - clearly you don't have such a script or you would have noticed that half of your process freelists were always empty and half were at the expected levels of usage. (The thing you showed on your website was a script that was attempting to check the number of free blocks associated with each freelist group block - which is something completely different.)

    I assume that your "discussion" with Rich Niemiec consisted of the following letter and reply posted to Oracle Magazine in April 2003:
    I found an error in "Advanced Tuning with Statspack" in your January/ February 2003 issue. Rich Niemiec writes that in the event of a wait on a segment header to increase the Freelist groups. Freelist groups only apply to Oracle9i RAC systems, and have nothing to do buffer busy waits on non-RAC systems.
    Donald K. Burleson
    Freelist groups do, in fact, have some benefits apart from Oracle9i RAC. MetaLink says that Freelist groups can have a positive impact in an exclusive environment (non-RAC) by helping reduce contention on the segment header. However, I should point out that segment header block contention can be addressed without multiple Freelist groups, for example, by increasing the pctfree/pctused gap or by partitioning the segment.
    Rich Niemiec
    (It looks like you may not be prepared to take Rich Niemiec's word about freelists and freelist groups after all.)

    Would a Metalink Note do as documented evidence issued by Oracle Corporation ? Metalink notes aren't always correct, of course, but I could also supply a couple of block dumps that help to confirm the point. (Actually, even if it's not documented by Metalink, wouldn't a few block dumps be fairly convincing anyway ?)

    Regards
    Jonathan Lewis


    +"If you claim that something is true, I will examine the evidence+
    +which supports your claim; if you have no evidence, I will not+
    +accept that wat you say is true and I will think you a foolish +
    +and gullible person for believing it so."+
    Richard Dawkins
  • 29. Re: Index rebuild
    706417 Explorer
    Currently Being Moderated
    "If you claim that something is true, I will examine the evidence
    which supports your claim; if you have no evidence, I will not
    +accept that *wat* you say is true and I will think you a foolish +
    and gullible person for believing it so."
    Richard Dawkins


    Knew it: Dawkins is illiterate.
1 2 3 4 11 Previous Next

Legend

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