1 2 3 4 5 Previous Next 73 Replies Latest reply on Nov 9, 2007 5:47 AM by damorgan Go to original post
      • 30. Re: When to rebuild indexes
        Richard Foote
        Hi Don

        While you ponder my previous question on what you do when an index still meets the rebuild criteria directly after a rebuild, let me spend a moment or two attempting to explain (yet again) inaccuracies and issues with the Quest article and "rules of thumb" you reference.

        Firstly, the case study in the article deletes some 28% of the table and then measures query performance before/after an index rebuilt. However, if this is the current state of the table, shouldn't the table be considered for a rebuild due to the extra costs of potential FTS ?

        Also, how often does one permanently delete 28% of rows for a table ? Not very often which suggests this case study highlights a specific issue, that massive deletes might impact index fragmentation (as also highlighted in my presentation).

        But the case study doesn't look at what happens when rows get reinserted. What if the deletes resulted in fully empty leaf nodes, these would have been automatically recycled by Oracle ? What if the index wasn't monotonically increasing, subsequent inserts might have cleaned out deleted entries ? Just because an index has deleted entries, it doesn't necessarily mean the index will continue to have them (as highlighted in my presentation). The rebuild may still be unnecessary.

        Then we have these test queries, notice anything unusual about them ? Each and every one of them doesn't actually reference the base table. ALL the queries are counts on the index column meaning that index only scans are performed. Wouldn't it be nice if perhaps some index operations that access the table were included as well to determine impact of index fragmentation on more "typical" index access plans.

        Notice also that 2 of the 4 queries, despite the index having a massive 28% deleted show no detrimental impact. This also highlights that queries that use the indexes are a significant factor in whether a rebuild is beneficial. What if the PK is only used for equality predicates ? Clearly such a "poorly" fragmented index does not hurt the performance of queries that only read small number of index row entries, as often typical in OLTP environments (as highlighted in my presentation).

        Look at the script to find these "naughty" indexes, you should recognise it, you recommend running a similar script yourself. It runs an Analyze Validate Structure on every index in the database (except SYS), that's each and every index in the entire database !! That means for the duration of each command, the underlining table is exclusively locked. That's locked, preventing all DML operations, for potentially DAYS in large databases.

        Seriously, how on earth can databases with any moderate availability requirements, of any moderate size possibly perform such operations ?

        I know of a couple of really messy methods, but Don, please explain to all how in the experiences you claim, working in large multi Terabyte database with 24 x 7 requirements, you can validate structure all indexes in a database ?

        And all for what ? To get information for these "rules of thumb".

        The first one, height of an index (which btw you can get from dba_indexes anyway's). I'll ask again (I've actually asked this of you Don numerous times with no answer), what do you do if an index after a rebuild remains at the same height ? It stills matches the rebuild criteria so do you simply rebuild it again ? And again ?

        So here we have an index rebuild criteria in which clearly it's possible for indexes to meet the criteria but not require a rebuild.

        And it's possible for indexes to be incredibly fragmented (again see my presentation) and yet be of a height of 2 or 3 (or whatever) ?

        So the index height criteria potentially selects indexes that don't need a rebuild and potentially misses out on those that might. In other words, the criteria is totally and completely redundant and totally and completely wrong.

        As I've said in the past, it's much better if you simply rebuild all indexes that begin with the letters "M" "Y" "T" and "H" as it will do exactly the same as the height criteria, potentially select indexes that don't need a rebuild and ignore those that do, but it does so with far less overheads :)

        And finally the deleted space criteria. Unfortunately, it doesn't factor in such minor things such as the fact it may vastly under-estimate the true wasted space of an index as it doesn't cater for deleted entries that have been cleaned out by Oracle, it doesn't cater for the fact that the deleted space is potentially all reusable anyway's and it doesn't cater for the fact that performance may be totally unaffected by the deleted space anyway's.

        In short, it also selects indexes which may not benefit from a rebuild and miss out on some that do (although likely not to the same degree as the first rule of thumb).

        Again, see the presentation for more of the details.

        You see the problem here Don. You advocate running a script that would cripple most databases (larger ones for potentially days) to generate a rebuild criteria that selects indexes that don't need a rebuild while potentially missing out on some that do.

        In Brian's defense, he wrote this article (your "facts" and "proof") over 7 years ago and he's managed to move on and progress his understanding.

        Don, perhaps it's time for you to move on too ?

        Cheers

        Richard Foote
        • 31. Re: When to rebuild indexes
          108476
          Hi Richard,
          Note "never" and "rarely" are not the same thing ...
          Agreed.
          Firstly, your terminology is confusing here
          Agreed, fixed.

          A "proveable" benefit from a rebuild is not just about the "half empty" index structure itself, it's also about the access patterns. (i.e. Index FFS).
          Ummm, the *2003* presentation turns out to have been incorrect ... Are you claiming otherwise ?
          Yeah, sorry. This OTN article shows the segment advisor recommending an index for rebuilding:

          http://www.oracle.com/technology/oramag/oracle/05-may/o35tuning.html

          But within 10g, the index rebuild recommendation is about space reclaimation, not performance. I asked Graham Wood about the holy grail of findng indexes that might benefit from a performance perspective, and we agreed that it requires detailed workload analysis. Who knows? With the new 11g workload analysis tools, it may not be too far off . . . .

          It's tough to write code using AWR to track index access patterns, but it's possible, and the "after" data would show a measurable reduction in logical I/O . . . .
          • 32. Re: When to rebuild indexes
            51034
            The "sparse" index refers to "almost empty" blocks, not those on the freelist.

            So why recommend a rebuild of such indexes? Why not recommend, at best, a coalesce?

            I only recommend rebuilding for databases that sit idle (i.e. Sunday's). Remember, servers depreciate fast, regardless of use, and batch jobs can be created that rebuild indexes with absolute safety

            This is a revised version of the argument you made in one of your short pieces on your website, isn't it: you'll just waste your CPU cycles so why not put them to good use? It can't do any harm after all.

            Well, OK, I'll buy that: if you can find me a database that is sitting there at a weekend doing absolutely nothing, used by no-one, of no Earthly use to man nor beast, then I really won't mind you clobbering its disks and buffer cache to death with a completely pointless rebuild that achieves nothing.

            I mean, that's what it amounts to, doesn't it? Because unless you can posit that sort of totally idle database, the claim of "absolute safety" whilst doing a "just in case, may as well, can't do any harm" rebuild can't be right, can it? Because if the database is not 100% idle, then the rebuild may block, lock or degrade the performance for someone else -which means it's not a free gift exercise at all but an expensive, intrusive waste of time.

            But fair enough: if you're going to qualify your advice to make it clear that those are the conditions that have to prevail before it's sound, I'll happily agree with it.

            Does a rebuild frequently help performance? No. Does a rebuild sometimes help? Absolutely.

            Again, I'll buy this. You're saying, I think, that because you can't identify (or that it's a waste of effort or too expensive an effort to identify) which indexes, precisely, truly need to be rebuilt, you're going to rebuild all of them, just in case. And the reason I'll buy that is that there is an excellent case to be made for a pragmatic approach to DBAing, whereby you do indeed do things which careful (and expensive) analysis might suggest redundant. You do them because it's quicker and cheaper to do them and get no benefit from doing them than it is to take the careful, considered, analytical route.

            I would quibble that it's probably more pragmatic to say "Don't bother rebuilding anything at all until the one that really needs it stands out like a sore thumb because of its size/slowness/propensity to cause users to complain"

            But yes: so long as we are clear that your advice to rebuild is not based on any analytics but is simply a pragmatic game of chance -you might get lucky rebuilding the one index in 50 or 100 that actually needs it by taking the carpet-bombing approach- I can see that there is some utility in your rebuild advice.

            Both those conditions ("do it when you have a 100% idle database for hours at a time" and "do it to everything... you might just manage to hit the one index that actually needs it") are a long way off the supposedly "scientific" ratio and clustering factor measurement myths that have been peddled about by others as justifications for a rebuild before now. But that makes the advice a refreshing change, actually, and to me that means it constitutes a much more defensible and -dare I say it- more honest approach.

            Better pragmatism based on an entirely subjective assessment of cost/benefit than bogus ratios and factors, I think.

            It is indeed weird agreeing with you on such matters.
            • 33. Re: When to rebuild indexes
              108476
              Hi Richard,
              That's locked, preventing all DML operations, for potentially DAYS in large databases.
              Sure, I never suggested that it be run in production! (but I noted your valid comment and added a disclaimer)

              What do you think? Do we have a responsibility to keep the naive and reckless from trashing their databases?
              Also, how often does one permanently delete 28% of rows for a table ?
              It depends! I've seen large LIMS and ERP implementations that do huge batch updates on a weekly basis . . . .
              please explain to all how in the experiences you claim, working in large multi Terabyte database with 24 x 7 requirements, you can validate structure all indexes in a database ?
              I don't! In my world, too many IT managers "insist" on having their indexes rebuilt, without any concern for "proving" any benefit! I agree with you, most of the time it does not help performance (but it does sometimes too!), but scheduled rebuilds of volatile indexes do reclaim loads of disk space . . . .

              Bottom line (and please let me know if you disagree!):

              - Oracle indexes are built for runtime speed, and the Oracle SE's made a consious decision to sacrifice structure for high speed. That's why entries are only "logically deleted" from the tree . . . .

              - Single row lookups are never effected, but full-scan operations can run significantly longer if the index blocks are "half empty".

              - Any "proveable" index rebuild method would have to examine historical workloads and compare them to an indexes "density". Then, it would have to re-examine a subsequent workload and verify that full-scans did indeed fetch the index entries with less consistent gets.
              • 34. Re: When to rebuild indexes
                108476
                Hi Howard,
                Better pragmatism based on an entirely subjective assessment of cost/benefit
                If anybody could do it, you could. Seriously, this could be quantified. We may disagree, but I don't question your persistence and dedication!

                Howard, what do you think about the idea of examining AWR workload histories? The 11g SPA tool opens up a whole new approach to "proving" benefits by examining real workloads:

                http://searchoracle.techtarget.com/tip/0,289483,sid41_gci1265880,00.html
                • 35. Re: When to rebuild indexes
                  Richard Foote
                  Hi Don

                  You know, I think we're getting somewhere !!

                  I guess an issue I have with what you're written on this issue is that you haven't previously put in enough disclaimers !!

                  The obvious comments I would make would be why promote a method of capturing index statistics if you don't use the method yourself ? And if you don't suggest it be run it in Production, on what database do you run it on ?

                  I have never, ever met an IT manager that said "oh and Richard, if you work here and want to administer our databases, you must rebuild all our indexes, I insist", or "Richard, we have a performance issue that needs to be addressed and we insist that part of the problem resolution must involve rebuilding all our indexes, we simply insist".

                  I would find such request totally bizarre and I would (in my typical manner) tell them precisely where they can put their "insist" request.

                  Most indexes, the vast majority run at 75% usage. Many others, those that monotonically increase, run at 100%. So on average, I would say 20% of space is likely to be sitting idle. But the vast vast majority of the 20% will be used anyways. In most databases, if you were to rebuild every index, and have a default pctfree of 10%, you would reclaim about 10% of space. And like I said, most of that 10% is going to be used anyways.

                  Having performed this scheduled rebuild, time becomes somewhat precious because you now have a much increased risk of performing block splits. These will:

                  a) hurt performance as the overheads of a block split are potentially significant
                  b) hurt disk space usage as these blocks with 50-50 splits now have leaf nodes with 50% of free space.

                  At the end of the day, temporarily reclaiming 10% disk space is simply not worth the overheads and performance risks.

                  Index entries and generally logically deleted at the time of the DML but may be physically deleted by many subsequent operations. They don't necessarily remain logically deleted.

                  "Full Scan" and large range scan operations may run slower if an index is badly fragmented (my presentation has examples of this), but how much slower and whether it impacts response times or scalability depends on various factors such as the caching characteristics of the index, the ratio of index blocks to table blocks accessed, whether index costs impacts the final execution plan, etc. The query "zone" (my presentation explains what I mean by this) is a critical factor.

                  The key point I make is that these problematic indexes are very rare and it's generally totally overkill and in many cases totally impractical to either periodically rebuild all indexes or validate the structure of all indexes.

                  In the end, the "secret" is to truly understand how indexes work and are maintained by Oracle and to have a good understanding of the data structures and processes that run in the databases one administers so one can determine which indexes may be problematic.

                  Our largest production system has just under 10,000 indexes (the database is approximately 2 TB). In the time I've been there, 2 of them have been found to have caused performance issues resolved by them being rebuilt.

                  Just 2.

                  Cheers

                  Richard
                  • 36. Re: When to rebuild indexes
                    orawarebyte
                    MYTHS

                    (from the presentation)
                    Oracle B-Tree Index Internals:
                    Rebuilding The Truth (updated October 2007)(PDF 3814kb)
                    by Richard Foote. You can also download it compressed in zip format here!


                    richard the above link is not getting opened.

                    Khurram
                    • 37. Re: When to rebuild indexes
                      Richard Foote
                      Hi Khurram

                      I just tested the links on this page and they appear to work:

                      http://www.miracleas.dk/index.asp?page=169&page2=276&page3=179

                      I haven't spoken to Mogens but possibly everyone trying to download the presentation all of a sudden is causing problems ?

                      I might need to talk to Radiohead and find out their secret ...

                      For those that don't understand the Radiohead reference, don't worry about it !!

                      Cheers

                      Richard
                      • 38. Re: When to rebuild indexes
                        108476
                        I have never, ever met an IT manager that said "oh and Richard, if you work here and want to administer our databases, you must rebuild all our indexes, I insist",
                        Sadly, I have. . . .

                        The managers wife used to work for Oracle, and every time I would complain I was told that "his wife said so". I was forced to completely rebuild an 80 gig database every weekend, it sucked, big time.
                        I would find such request totally bizarre and I would (in my typical manner) tell them precisely where they can put their "insist" request.
                        Oh yeah, I almost did, and boy did he get mad when I questioned his wife's credibility (after all, she was an ex-Oracle employee)!

                        He made it clear that a full weekly reorg was an absolute job requirement, and I had a family to support. . . .

                        It's the same deal with hardware-based tuning. From a personal profit motive I'd much rather correct the root cause, (thousands of hours of work!), but DBA's rarely make the financial decisions.
                        In the time I've been there, 2 of them have been found to have caused performance issues resolved by them being rebuilt.
                        It agree that's it's "rare" overall, but for some specialized systems it's a serious issue, especially for ERP's that do batch-oriented loads and deletes . . . .

                        Oh, good luck on your OOW presentation. I'd like to be there, but I'm swamped.

                        Message was edited by:
                        burleson
                        • 39. Re: When to rebuild indexes
                          Richard Foote
                          Hi Don

                          Not entirely sure I'll present at the OOW Unconference yet, still deciding.

                          To anyone going to OOW, is this something that would be of interest ?

                          http://wiki.oracle.com/page/Advanced+Index+Internals
                          http://wiki.oracle.com/page/Oracle+Indexes%3A+Tips%2C+Traps+and+Tit+Bits

                          Cheers

                          Richard
                          • 40. Re: When to rebuild indexes
                            Maran Viswarayar
                            Thanks ALL

                            We are the one who are benefited the most from this post..

                            Outstanding explanations and Justifications


                            Thank You
                            Elamaran
                            • 41. Re: When to rebuild indexes
                              damorgan
                              Richard Foote wrote:
                              I have never, ever met an IT manager that said "oh and Richard, if you work here and want to administer our databases, you must rebuild all our indexes, I insist",

                              Don Burleson responded:
                              Sadly, I have. . . .

                              And my comment is that professional ethics hopefully have led you to refuse to do so.
                              Physicians take the Hippocratic Oath and I wish that doing so was a required part of becoming a DBA.

                              "To practice and prescribe to the best of my ability for the good of my patients, and to try to avoid harming them."

                              Unnecessary procedures, in medicine, are a violation of that oath.
                              • 42. Re: When to rebuild indexes
                                405087
                                @Sybrand,

                                Regarding truncation:
                                Previous to Oracle 10 I would have agreed with you.
                                In 10 however, you cannot truncate tables with active foreign keys pointing to them (regardless whether the child table has any records or not).
                                This means that sometimes large scale deletions can be necessary. You can circumvent this with unwieldy scipts which disable, and later enable any FK constraint where the referenced table is the one being thruncated. but takes away the ease of truncate.

                                <snip>
                                If
                                you are performing massive deletes you would either
                                best truncate the table, or just drop the indexes so
                                you cut down on redo during reorganization.
                                <snip>
                                Sybrand Bakker
                                Senior Oracle DBA
                                regarding the rebuilding of tables:
                                currently we are using 2 versions of OWB:
                                The old production system uses 10.1, the new one uses 10.2 (paris).
                                The old OWB analyses tables at the end of the mapping but does that with a cascade=false, which means that indexes are not analysed, and thus quickly go out of sync, messing up your FK validation.
                                In OWB 10.2 this has been fixed
                                In either case this means that in the old production system we will need to rebuild indexes quite regularly.
                                • 43. Re: When to rebuild indexes
                                  Niall Litchfield
                                  regarding the rebuilding of tables:
                                  currently we are using 2 versions of OWB:
                                  The old production system uses 10.1, the new one uses
                                  10.2 (paris).
                                  The old OWB analyses tables at the end of the mapping
                                  but does that with a cascade=false, which means that
                                  indexes are not analysed, and thus quickly go out of
                                  sync, messing up your FK validation.
                                  I don't understand this remark, or at least my understanding of it suggests that it is incorrect!. The plain reading of this is that unless your indexes are analyzed they do not accurately index the table they are built on - that can't be correct surely. Not analysing/gathering stats on indexes would certainly be an unfortunate omission from a performance point of view, but I don't see how it would mess up FK validation? Or do you perhaps mean that the index is not rebuilt (in 10.1) following the table rebuild?
                                  • 44. Re: When to rebuild indexes
                                    Richard Foote
                                    Hi Niall,

                                    You're not alone, I'm scratching my head as well ?

                                    Cheers

                                    Richard