1 7 8 9 10 11 Previous Next 163 Replies Latest reply: Oct 22, 2009 5:23 PM by Hoek Go to original post RSS
      • 120. Re: Index rebuild
        Hans Forbrich
        Don Lewis wrote:
        No. Because unlike a test case, I have a human brain.
        :-)
        • 121. Re: Index rebuild
          108476
          After getting in the "last word", Lewis now want to stifle our discussion, and is petitioning Justin to close this thread:

          Index Rebuild

          But we are making progress.

          A few years ago, he would have denied these facts:
          On one occasion I even advised a client to coalesce a critical index once per hour to deal with a very specific activity
          There are a few special cases where rebuilding an index will cause subsequent performance problems - and even a coalesce could cause problems.
          As to his second observation, a least he now acknowledges that the index tree structuire effects SQL performance.

          But it's so off-the-wall that I don't suspect that he will even try to show us any evidence.

          It's funny, this is one time when an artificial test case WOULD be appropriate!

          I have the utmost respect for Jonathan's considerable skill at gerrymandering test case results, but I don't think that he can show a single reproduceable where an index rebuild results in significant performance degregdation for subsequent SQL . . .
          • 122. Re: Index rebuild
            706417
            I take exception to his calling for a lock-down, too.

            This is a forum*, isn't it?

            Regards - Don Lewis





            * From http://www.thefreedictionary.com/forum

            fo·rum (fôrm, fr-)
            n. pl. fo·rums also fo·ra (fôr, fr)
            1.
            a. The public square or marketplace of an ancient Roman city that was the assembly place for judicial activity and public business.
            b. A public meeting place for open discussion.
            c. A medium for open discussion or voicing of ideas, such as a newspaper, a radio or television program, or a website.
            *2. A public meeting or presentation involving a discussion usually among experts and often including audience participation.*
            3. A court of law; a tribunal.

            From Lewis' brain:
            fo·rum (fôrm, fr-)
            n. pl. fo·rums also fo·ra (fôr, fr)
            1.
            a. A place where Jonathan Lewis reigns supreme and lays down the law like he owns the place.
            • 123. Re: Index rebuild
              108476
              A place where Jonathan Lewis reigns supreme and lays down the law like he owns the place.
              If he had been forthcoming in answering our questions, I might understand . .

              But he didn't answer our questions, nor provide any evidence or proof whatsoever.

              - What is the nature of scripts that detect indexes that will benefit from rebuilding? - I'm hoping that somebody might share some details.

              - We need to put to bed the nonsense that cases exist whereby a freshly rebuilt index is somehow sub-optimal. It's that's true, it's a bug that needs to be fixed.

              - Everyone now acknowledges that are are cases where indexes benefit from scheduled maintenance, let's describe these conditions, in detail . . .

              The problem is that Lewis & Foote mistakenly think that they "understand" this phenomenon, but they have only scratched the surface.

              They have happened upon a few isolated cases out of an entire universe of possibilities, and they don't have enough evidence from their isolated test cases to draw any general conslusions. Their real problem is that test cases are useless, and their whole "Oracle scientist" methodology is fatally flawed.

              Desipte their arrogant and condescending tone, (and their haughty posture as "Oracle scientists"), they have yet to generalize their findings into guidelines that everyone can use. NOT ONE SINGLE GUIDELINE.

              Enquiring minds want to understand that "nature" of this "index too large" phenomenon:

              - how does index key size and block size effect fragmentation?

              - Why does the need to rebuild indexes happen more frequently on ASSM indexes?

              - Why do indexes with multiple freelists groups show more index fragmentation?

              - What is the nature of the delete DML that causes "index browning"?

              - Is there really any difference between the terms Index browning, unbalanced indexes, and fragmented indexes?  If so, what are they?

              These are the outstanding questions. . .

              Let's see, maybe we might get some outstanding answers . . .
              • 124. Re: Index rebuild
                601262
                burleson wrote:
                A place where Jonathan Lewis reigns supreme and lays down the law like he owns the place.
                If he had been forthcoming in answering our questions, I might understand . .

                But he didn't answer our questions, nor provide any evidence or proof whatsoever.

                - What is the nature of scripts that detect indexes that will benefit from rebuilding? - I'm hoping that somebody might share some details.

                - We need to put to bed the nonsense that cases exist whereby a freshly rebuilt index is somehow sub-optimal. It's that's true, it's a bug that needs to be fixed.

                - Everyone now acknowledges that are are cases where indexes benefit from scheduled maintenance, let's describe these conditions, in detail . . .

                The problem is that Lewis & Foote mistakenly think that they "understand" this phenomenon, but they have only scratched the surface.

                They have happened upon a few isolated cases out of an entire universe of possibilities, and they don't have enough evidence from their isolated test cases to draw any general conslusions. Their real problem is that test cases are useless, and their whole "Oracle scientist" methodology is fatally flawed.

                Desipte their arrogant and condescending tone, (and their haughty posture as "Oracle scientists"), they have yet to generalize their findings into guidelines that everyone can use. NOT ONE SINGLE GUIDELINE.

                Enquiring minds want to understand that "nature" of this "index too large" phenomenon:

                - how does index key size and block size effect fragmentation?

                - Why does the need to rebuild indexes happen more frequently on ASSM indexes?

                - Why do indexes with multiple freelists groups show more index fragmentation?

                - What is the nature of the delete DML that causes "index browning"?

                - Is there really any difference between the terms Index browning, unbalanced indexes, and fragmented indexes?  If so, what are they?

                These are the outstanding questions. . .

                Let's see, maybe we might get some outstanding answers . . .
                Mr. Burleson,

                You bring up many good questions. As someone who frequently refers to themselves as an expert and cites their lengthy experience and empirical observations, what are your answers to your questions? To have the most beneficial discussion you should provide your position and cite supporting evidence, not just be critical of others responses and repeatily just raise question with others' answers. Lawyers generally behave that way, not technologists.


                Regards,
                Greg Rahn
                http://structureddata.org
                • 125. Re: Index rebuild
                  Jonathan Lewis
                  burleson wrote:

                  - What is the nature of scripts that detect indexes that will benefit from rebuilding? - I'm hoping that somebody might share some details.
                  There are two on my website ( http://www.jlcomp.demon.co.uk ) that have been there for four or five years.
                  - We need to put to bed the nonsense that cases exist whereby a freshly rebuilt index is somehow sub-optimal. It's that's true, it's a bug that needs to be fixed.
                  There is an article on my website ( http://www.jlcomp.demon.co.uk ) that has been on my website for four or five years giving an example of why a rebuilt index can cause problems. (It is nice to know, though, that you have finally worked out - after being told to think about it several times - that you can reduce the threat by setting an appropriate pctfree).

                  >
                  - Everyone now acknowledges that are are cases where indexes benefit from scheduled maintenance, let's describe these conditions, in detail . . .
                  Everyone (who understands indexes) has known for a very long time that there are cases where an index rebuild (or more often coalesce) could afford some benefit - it is only your straw-man argument that makes the claim that indexes never need to be rebuilt. There are several articles on my website ( http://www.jlcomp.demon.co.uk ) demonstrating this fact - a couple of them are even the papers writing up presentations of mine that you have attended at IOUG conferences in the last few years.

                  >
                  The problem is that Lewis & Foote mistakenly think that they "understand" this phenomenon, but they have only scratched the surface.
                  Opinions on that may vary - we have to remember that you are only a DBA, of course, and do not expect to have time to investigate the behaviour of indexes properly in case you are fired for wasting your employer's time.
                  They have happened upon a few isolated cases out of an entire universe of possibilities, and they don't have enough evidence from their isolated test cases to draw any general conslusions. Their real problem is that test cases are useless, and their whole "Oracle scientist" methodology is fatally flawed.
                  I would be surprised if you (specifically) could find even twenty different CLASSES of index behaviour that have not been covered by our investigations. (Remember, people like Lewis & Foote are often hired to investigate and solve problems and therefore have the time to look carefully at big systems running production volumes at high speed ... and Lewis & Foote are both capable of deriving simplified models to explain critical features of any undesirable behaviour they see. Understanding how to identify and model the salient features of a complex system is a skill that few people have, and you are clearly not one of the few. (There is a article on my website ( http://www.jlcomp.demon.co.uk ) demonstrating this point.
                  Desipte their arrogant and condescending tone, (and their haughty posture as "Oracle scientists"), they have yet to generalize their findings into guidelines that everyone can use. NOT ONE SINGLE GUIDELINE.
                  Guidelines - see my website.
                  See also my previous posting on this thread.

                  Enquiring minds want to understand that "nature" of this "index too large" phenomenon:
                  Enquiring minds think and investigate - they don't just keep trying to distract attention. However, you are only a DBA who you would fire if you stopped to think - so I understand your frustration. There is, however, an article on my website ( http://www.jlcomp.demon.co.uk ) that may help you. It's one I wrote a few years ago and had published on dbazine while you were still billed as an editor there.
                  - how does index key size and block size effect fragmentation?
                  See the article on my website ( http://www.jlcomp.demon.co.uk ) about large keys and excessive leaf block splits - it is a paper based on a presentation I did at IOUG a few years ago (you wrote a couple of articles about it at the time.)

                  - Why does the need to rebuild indexes happen more frequently on ASSM indexes?
                  Apart from the various space-management bugs in ASSM relating to the timing of resetting bitmaps, do you have any examples of this ? I would be interested to see some comments on the phenomenon as I have not yet come across it at a client site, and so not had any need to investigate it. Since it's you, anecdotal evidence will suffice - provided it gives some idea of context.

                  Update: It's just occurred to me that you may be thinking of the (predictable) increase in wasted space that appears in non-unique indexes with time-dependent keys of relatively low counts of distinct values. It's the same as the effect you would expect when using multiple freelists.

                  - Why do indexes with multiple freelists groups show more index fragmentation?
                  That depends on what you mean by index fragmentation, and even what you mean by "more" - but there are some obvious effects to consider that depend whether you are running RAC or single instance.

                  - What is the nature of the delete DML that causes "index browning"?
                  Personally I think the expression "index browning" is a silly one - it does nothing to explain what is going on and only introduces another pointless term that has to be explained. The nature of the "delete DML" that causes browning is that you use "delete DML" to delete entries from an index. The timing between the entry being marked for delete and the final cleanout is longer than it is for a table - but the phenomenon is not difficult to comprehend.

                  - Is there really any difference between the terms Index browning, unbalanced indexes, and fragmented indexes?  If so, what are they?
                  Not really - they are all sloppy expressions that carry no useful information, need to be explained, serve only to cause confusion, and really should not be used.

                  These are the outstanding questions. . .
                  Let's see, maybe we might get some outstanding answers . . .
                  There are at least two meanings for the word "outstanding" ;)



                  Regards
                  Jonathan Lewis

                  +"Science is more than a body of knowledge, it is a way of thinking"+
                  Carl Sagan

                  Edited by: Jonathan Lewis on Oct 16, 2009 6:59 PM
                  Added comment to ASSM response.
                  • 126. Re: Index rebuild
                    723408
                    burleson wrote:
                    Hi Alan,
                    all things in the database stay the same but through monitoring of indexes
                    Do you have a script that you use to detect index rebuilding candidates?
                    Hi Alan,
                    The model for assessing whether an index needs to be rebuilt is some complicated mathematical equation > > which an ex colleague created.
                    Great!
                    From experience this has always proved to be correct over a wide range of databases.
                    That's how good scripts are created, a process of successive refinement and empirical testing.
                    Is it possible to share this script?
                    Hi Alan,
                    Is it possible to share your index rebuild detection script?
                    Why reinvent the wheel?
                    Burleson,

                    If you are the lead promoter for rebuilding index to solve the performance related issues, then why don't you publish your script here to find the index,which need rebuild.

                    Based on your advice i rebuild all index used on a complex data warehouse query, which was having and I/O bottle neck issue, in test system and it took more than 2 times the original execution. If the index rebuild is safe, then why it took more than double time?. I can understand if the performance is same after the index rebuild. But it become worst. Finally i have to transport the tablespace from production to make it normal. Yes, i have done stat collection and all after the index rebuild.

                    Regards,
                    • 127. Re: Index rebuild
                      6363
                      user11919636 wrote:

                      Burleson,

                      If you are the lead promoter for rebuilding index to solve the performance related issues, then why don't you publish your script here to find the index,which need rebuild.
                      Possibly because when previous indicators have been identified they have been proven to be incorrect.

                      Maybe the most obviously wrong one is the clustering factor indicator -

                      http://www.praetoriate.com/t_grid_rac_external_tables.htm
                      The clustering factor column, in listing 10.21, shows how well the index is ordered in comparison to the base table. If the value for the clustering factor is near the number of table blocks, it means the index is well ordered; conversely, if the value is near the number of rows in the table, the index is not well ordered (unless the row size is close to blocksize). For indexes with high clustering factors, consider rebuilding, as a high clustering factor indicates that, under index scan conditions, the same blocks will be read numerous times.
                      The clustering factor shows how closely the order of the rows in the table match the order of the the index entries. Since an index is by definition ordered, then rebuilding it will never change the clustering factor. To get a better clustering factor it is the table that needs to be reorganized.
                      • 128. Re: Index rebuild
                        624104
                        Ack

                        Edited by: TheDudeNJ on Oct 16, 2009 3:14 PM
                        • 129. Re: Index rebuild
                          108476
                          Hi Greg,

                          I don’t claim to have all of the answers, and if you examine past incarnations of this topic, you will note that the "Oracle Scientists" previously have been quite adamant that index rebuilding is a waste of time! At least this time, their experience is changing, and we are making progress.
                          what are your answers to your questions?
                          I have almost a dozen notes on this subject, as new evidence is revealed, I try to keep them updated:

                          http://dba-oracle.com/googlesearchsite_proc.htm?cx=000522505899594707971%3A4-ldikxixw4&cof=FORID%3A10&ie=UTF-8&q=index+rebuilding%7Crebuild#0

                          Here are my latest notes:

                          http://www.dba-oracle.com/t_index_rebuilding_issues.htm
                          you should provide your position and cite supporting evidence
                          We tried this before, remember?

                          You callously demanded evidence, and I spent time convincing our client to let you have direct access to see it for yourself.

                          Then you refused to sign the NDA.  That was not something we expected from an employee of Oracle Corporation who demanded proof.

                          It was just a set-up, when all I did was try try to show you proof.

                          Forget it.
                          • 130. Re: Index rebuild
                            108476
                            Hi Jonathan,
                            There are two on my website ( http://www.jlcomp.demon.co.uk ) that have been there for four or five years.
                            You claim to have proof, and yet you expect people to filter through your whole web site to find them?

                            Please, kind sir, take a second and post the links to the evidence.

                            Please, post a summary here . . .
                            There is an article on my website ( http://www.jlcomp.demon.co.uk ) that has been on my website for four or five years giving an example of why a rebuilt index can cause problems
                            If there was, then why not just paste-in the answer?
                            you are only a DBA
                            Yes.

                            I'm just a mere DBA, who had been hands-on with real-world databases for twenty years more then you acknowledge . . . .
                            do not expect to have time to investigate the behaviour of indexes properly
                            in case you are fired for wasting your employer's time.
                            You probably don't remember, but I asked you about this topic on the phone many years ago, and your exact words to me were:

                            *"I want to wait for somebody to pay me to figure that out".*

                            Surely you remember that phone call?

                            And yes, I'm only a DBA, but I'm a lot smarter than you are, and I have a better memory too . . .
                            There are several articles on my website ( http://www.jlcomp.demon.co.uk ) demonstrating this fact
                            Right, sure there are . . .
                            See the article on my website ( http://www.jlcomp.demon.co.uk ) about large keys and excessive leaf block splits
                            If that's true, why are you reluctant to give us an executive summary?
                            Personally I think the expression "index browning" is a silly one
                            As a teacher, I think it's brilliant.

                            It explains visually exactly what it looks like, just a tree after being struck by lightening . . .
                            Guidelines - see my website
                            Can you please take a minute to paste them in here?
                            • 131. Re: Index rebuild
                              108476
                              Hi Pointless,
                              Possibly because when previous indicators have been identified they have been proven to be incorrect.
                              You still believe that "one negative test case proves something wrong" nonsense?

                              Test cases are painfully easy to rig!

                              I can write one to disprove anything you want, it's easy, try it!

                              BTW, re-read the credits on that article. As you see, I did not write that!

                              I spend time gathering and dissemenating Oracle information from some of the world's best Oracle experts:

                              http://www.rampant-books.com/authors.htm

                              Please don't assume that I wrote everything, I only contribnute a small fraction of the knowledge . . .
                              Since an index is by definition ordered, then rebuilding it will never change the clustering factor.
                              That's not how I read it, but I agree that sentence might be misunderstood by some people.

                              That's a book excerpt by Mike Ault that I purchased rights to, but I fixed it to make this clear that he is referring to rebuilding the table, not the index.

                              Is this better?

                              "For high use indexes with high clustering factors, consider rebuilding the table in the same sequence as the index, as a high clustering factor indicates that, under index scan conditions, the same blocks will be read numerous times."

                              Thanks for pointing that out!

                              BYW, there are times when you want to force an index to be in sequence with the index that services most of the queries, that's what sorted hash clusters are used for!

                              For example, consider a system whenere 1,000 clericals call-up orders for a customer all day.

                              Reorging the order rows to group them by customer ID will radically reduce I/O overhead!

                              Instead of doing I/O against 10 blocks to gat all the orders, you only have to access one order block.
                              • 132. Re: Index rebuild
                                108476
                                Hi Don,

                                Evidently, Justin misunderstood, and locked the wrong thread . . .

                                Index Rebuild

                                He stated "*we do not need to open this wound again*" . . .

                                Oh well . . .

                                I guess our quest for the truth is over.
                                A place where Jonathan Lewis reigns supreme and lays down the law like he owns the place.
                                Remember the time Lewis started a topic in the community forum titled "*please ban user burleson*"?

                                Out of respect for Justin's wishes for us to stop learning about this topic, I'm going to stop responding now . . .

                                Bye!
                                • 133. Re: Index rebuild
                                  Jonathan Lewis
                                  burleson wrote:

                                  For example, consider a system whenere 1,000 clericals call-up orders for a customer all day.
                                  I'd encourage that customer to see a doctor - he (or she) clearly has a memory problem. Next thing you know he (or she) won't just be forgetting his (or her) orders all the time, he (or she) will be remembering things that never happened.

                                  With sympathy.
                                  Jonathan Lewis
                                  • 134. Re: Index rebuild
                                    601262
                                    burleson wrote:
                                    Hi Greg,

                                    I don’t claim to have all of the answers, and if you examine past incarnations of this topic, you will note that the "Oracle Scientists" previously have been quite adamant that index rebuilding is a waste of time! At least this time, their experience is changing, and we are making progress.
                                    what are your answers to your questions?
                                    I have almost a dozen notes on this subject, as new evidence is revealed, I try to keep them updated:

                                    http://dba-oracle.com/googlesearchsite_proc.htm?cx=000522505899594707971%3A4-ldikxixw4&cof=FORID%3A10&ie=UTF-8&q=index+rebuilding%7Crebuild#0

                                    Here are my latest notes:

                                    http://www.dba-oracle.com/t_index_rebuilding_issues.htm
                                    you should provide your position and cite supporting evidence
                                    We tried this before, remember?

                                    You callously demanded evidence, and I spent time convincing our client to let you have direct access to see it for yourself.

                                    Then you refused to sign the NDA.  That was not something we expected from an employee of Oracle Corporation who demanded proof.

                                    It was just a set-up, when all I did was try try to show you proof.

                                    Forget it.
                                    Mr. Burleson-

                                    When I use the word evidence I'm looking for supporting data that can be publicly discussed on this forum. I'm very much of the opinion that if one makes a claim they should 1) explain what data they looked at to come to that conclusion (supporting facts) and 2) their overall interpretation of how/why that event is happening. This gives the reader a basic what happened and why it happened as well as how to look for (identify) the symptoms in their environment to determine if the same event is occurring there.

                                    It is really unfortunate and unprofessional (to say the least) that you have to resort to continuously bringing up this whole NDA thing. First, the conversation took place on this forum, not via business email or official business channels so I hardly consider it a serious business matter. Second, I repeatedly said that I was only interested in sanitized information that could be discussed publicly and you could not deliver that for whatever reason(s). Third, I told you that an NDA would have to be between said customer and Oracle Corp, not me an individual, but you didn't pursue that and now you want to use it as some "dark cloud" to hang over me on this forum which is certainly quite ridiculous and very unprofessional. All of this, I might add, is certainly not something I would expect from someone who claims to be an Oracle expert and professional.

                                    So let it be known publicly your attempt to show "proof" .

                                    Regards,
                                    Greg Rahn
                                    http://structureddata.org