1 2 3 4 5 Previous Next 73 Replies Latest reply: Nov 8, 2007 11:47 PM by damorgan Go to original post RSS
      • 45. Re: When to rebuild indexes
        311441
        Hi Don

        You initially said "In my world, too many IT managers "insist" on having their indexes rebuilt" but you've just mentioned one specific case here. Have you worked for various other IT managers that have also insisted you rebuild indexes or was this just some really bizarre isolated case ?

        Assuming you have full control over a database which I'm sure you have countless of times, what do you personally do regarding index maintenance ? Do you:

        1) Simply rebuild all indexes periodically ?
        2) Validate structure all indexes in a database and use some rebuild criteria to choose supposedly suspect indexes ?
        3) Have another approach ?
        4) Do nothing ?

        Assume the database is a typical 24 x 7 with small maintenance windows and of a moderate size, say 1 TB.

        What do you actually do ?

        Cheers

        Richard
        • 46. Re: When to rebuild indexes
          ben23
          In OWB 10.2 this has been fixed
          Sounds like an OWB person describing a DB 10.1 bug fixed in 10.2.
          • 47. Re: When to rebuild indexes
            Aman....
            Hi sir,
            EXCELLENT discussion!
            It will take some time to digest yours,HJR sir's posts though :).
            Thanks and regards,
            Aman....
            • 48. Re: When to rebuild indexes
              108476
              Hi Richard,

              First, I hope you can tell me your opinion, please, relating to tips for advanced DBA's only:

              - Do we have a responsibility to keep the naive and reckless in cyberspace from trashing their databases? Do we need a "Be careful, this coffee is hot", disclaimer on every page?

              We Americans are IDIOTS when it comes to product disclaimers:

              http://money.cnn.com/2005/01/06/news/funny/warning_labels/index.htm

              Is it like that in Oz?
              What do you actually do ?
              The answer is as varied as the shop, but I rely on empirical methods whenever possible. I hate guessing. I'm not going to cop-out with an "it depends", so I wrote this, just for you:

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

              As usual, please review it for me and point-out any discrepancies with your school of thought, please . . . .

              Oh Richard, don't be shy; tell me how you really feel ; )
              • 49. Re: When to rebuild indexes
                113886
                "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.", "He made it clear that a full weekly reorg was an absolute job requirement, and I had a family to support. . . ."

                Two words, cron and bash, while it should be unecessary to rebuild indexes, especially in a system that large. If you must, and you have the space, you should never need to be there for the rebuild.

                "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."

                Prove why something doesn't work, and demonstrate it, and it should not matter who makes the reasons, unless of course they are blinded by superstition.

                ">> In the time I've been there, 2 of them have been found to have caused performance issues resolved by them being rebuilt."

                Two indexes out of how many indexes, and how many times those indexes were being rebuilt. Not to mention that they might have been some other problem was causing the real issue, but rebuilding the indexes may have obfuscated the underlying problem.

                "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 . . . ."

                In my last job I worked on a decision support system that did 50,000 - 100,000 row inserts into a table per transaction throughout the day. From time to time this would cause the table to get analyzed during the day. Thus locking the table and preventing inserts while it was being analyzed.

                The table consisted of a batch id, natural key, and two sequences. The inserts/query were taking 10 - 20 minutes. I dropped the two sequence based columns as being un needed, and deleted stats on the table, making sure that Oracle never computed stats again. I losing two indexes based on the columns dropped. The insert/query process dropped to a matter of seconds. Which caused me to understand how sequences can have a serious impact, and that there is no reason to lock the table everytime someone does a large insert, thus preventing conccurency in the database.

                I could have looked at rebuilding the indexes on the table daily, hourly, etc, or I could have dropped the dead weight of all of the processing that was not needed. I also reformatted the query into an inner join from a non-correlated query using an in clause. Rebuilding indexes as a preventative measure is like hitting you tv when you get static on the screen.

                To paraphrase Macbeth this topic is "...a tale told by an idiot, full of sound and fury, signifying nothing. "

                Happy Friday, enjoy your index rebuilding!!!
                • 50. Re: When to rebuild indexes
                  damorgan
                  "For 'tis the sport to have the (software) enginer Hoist with his own petard"
                  ~ Hamlet
                  • 51. Re: When to rebuild indexes
                    108476
                    Hi Richard,
                    Have you worked for various other IT managers that have also insisted you rebuild indexes or was this just some really bizarre isolated case ?
                    Not isolated, especially in larger IT shops with hundreds of instances. I worked for a shop with over 25 DBA’s, and the VP (a huge goof), saw that a reorg helped one database and mandated that all databases be reorged periodically, regardless of justification! It was right there is the DBA’s MBO’s . . . .

                    But it’s not so much just the indexes being rebuilt, but a full reorg. Interestingly, senior Oracle consultants still encourage periodic reorgs, especially when it mean more dollars for Oracle Corporate consulting, IMHO.

                    IT managers like to show their end-users that they are committed to them and will unilaterally announce a reorg for publicity purposes.

                    Often as not, the end-users will respond by claiming that the reorg did wonders, even though there is no measurable change to the workload after the reorg. Superstition, or expectation, I see it frequently.

                    On the other hand, I've done hundreds of "legit" reorgs that did indeed help, big time.
                    • 52. Re: When to rebuild indexes
                      108476
                      Hi Gandolf,
                      If you must, and you have the space, you should never need to be there for the rebuild.
                      In theory, agreed. But when it's MY database, I like to be there.

                      As Peter Sellers said in the movie "Being there":

                      "I like to watch"!
                      Prove why something doesn't work, and demonstrate it, and it should not matter who makes the reasons, unless of course they are blinded by superstition.
                      It's more than superstition, it's ego too, IMHO. Some IT managers are over-confident jerks, with Ivy degrees and massive ego's (I call them "edicated idiots", all theory, no pragmatism). As they say: "You can always tell a Harvard man, but you can't tell him much":

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

                      Often as not, they simply respond "because I said so".
                      "...a tale told by an idiot, full of sound and fury, signifying nothing. "
                      PERFECT!
                      • 53. Re: When to rebuild indexes
                        108476
                        Hi Dan,

                        What a great topic! What are the ethical responsibilities of a DBA?

                        Please review and comment:

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

                        BTW, I was talking to Alex Algard awhile back (he lives in your neck of the woods), and I wrongly assumed that you were classmates at Stanford. Just curious, what year did you get your doctorate?
                        • 54. Re: When to rebuild indexes
                          311441
                          Hi Don

                          When you write an article that suggests validating the structure of all indexes in a database, rebuild an index if the height is greater than "x", use 32K block size for indexes, etc. etc., the concern is not for advanced DBAs. All advanced DBAs I work with or communicate with clearly understand the dangers, impracticalities and ultimate follies of such advice.

                          The problem of course lies with newbies, those inexperienced with Oracle, those that have an Oracle database thrust upon them because Oracle databases are self-tuning beasts that don't need much administration, etc.. It's these folk who look at such suggestions from a position of ignorance, from someone who's had much more experience with Oracle than they themselves, who might mistakenly be persuaded that such suggestions are a good thing.

                          Bad or misleading advice is always a danger, whether in the US, Australia or elsewhere, especially if written in a generalistic manner without supporting caveats, evidence or practical examples.

                          Your link doesn't currently work so I can't comment. I don't have much spare time between now and OOW anyways so any comprehensive review will likely have to wait until afterwards.

                          But I won't be too shy I promise :)

                          Cheers

                          Richard Foote
                          • 55. Re: When to rebuild indexes
                            108476
                            Hi Richard,
                            especially if written in a generalistic manner without supporting caveats, evidence or practical examples.
                            Ah, but what about advice that is targeted to senior DBA's?

                            I complied with your warning "don't do this in production", but it's so self-evident that it reminds me of this warning from a hair coloring package "Do not use as an ice cream topping."

                            I'll ask again "Richard, do you think that we have a responsibility to protect the naive and reckless among us?"

                            It's a serious issue. Every week I get calls for unplanned downtime because some DBA Bozo ran a script just to "see what it does":

                            http://www.dba-oracle.com/t_dilettantes.htm
                            When you write an article that suggests validating the structure of all indexes in a database, rebuild an index if the height is greater than "x",
                            If I did suggest that, I'll retract it today, as soon as I'm on the air again.
                            use 32K block size for indexes, etc. etc.,
                            That I stick by, but ONLY for indexes that experience range scans and index FFS, and NOT FOR BEGINNERS. Robin Schumacher has a reproduceable proof, if you want to try it yourself:

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

                            Oh, also this OTN thread showing a 3x faster performance in a larger blocksize:

                            Multiple block size advantages.
                            Your link doesn't currently work so I can't comment.
                            Oh, sorry, I'm in the middle of a reorg . . . . ; )

                            Check back after 1:00 PM EST . . . .
                            any comprehensive review will likely have to wait until afterwards.
                            Thanks. Ping me via e-mail, so I don't miss it, please . . . .
                            • 56. Re: When to rebuild indexes
                              311441
                              Hi Howard

                              I noticed in your Blog entry regarding all this:

                              http://www.dizwell.com/prod/node/1160

                              you suggest an index needs only to be in a DMT for the performance issues relating to the re-growth of an index to be significant. If the index were to be in a LMT, then these issues are not likely to apply.

                              Note this is incorrect.

                              The possible performance issues relating to an index "regrowth" are mainly related to the significant overheads in Oracle having to split a block on the fly, redistribute the index row entries, having to modify the branch block, extra LIOs in having to access and modify the leaf pages on either side of the split block and possibly having to split the related branch block as well.

                              These overheads apply to LMTs just as it does to DMTs.

                              I discuss this in my index rebuild presentation and show a little demo in a LMT where insert performance has been impacted.

                              It's only the allocation of additional extents that's less problematic in LMTs.

                              Cheers

                              Richard Foote
                              • 57. Re: When to rebuild indexes
                                51034
                                you suggest an index needs only to be in a DMT for the performance issues relating to the re-growth of an index to be significant.

                                No, I don't suggest any such thing. You need to read more carefully. The initial blog piece talked about a performance hit "in certain circumstances". Circumstances, note: in the plural. It then mentioned one of them, the one which Tom has written about before and which I judged likely to be most in the forefront of people's minds, the one relating to index extent re-acqusition.

                                You may not like my judgement call, and you may not agree with the way I write. But don't claim that I said "extent re-acquisition is the only issue" when I didn't.

                                And if you were prepared to read around a bit more Richard; had you been less keen on shouting "you are incorrect" and more keen on reading the entirety of what I write, you would have seen this, on exactly the same page you linked to:

                                The other thing that can happen is that your execution plans change as a result of having a lot of newly-compacted indexes and the change is for the worse. True, it could happen: but it would only have to happen once (and presumably -hopefully!- in a test database) and I think we could safely say at that point that you'd know not to rebuild that particular index or that particular bunch of indexes again. There are also other tools to be deployed to obviate this possibility: stored outlines, backed up & restored segment statistics and so on.

                                I think the possible change in execution plans a much more serious issue than the block split and leaf entry redistribution issues you now cite. Block splits and index entry redistribution will, of course, be happening in memory, not on disk. That's not a trivial issue, and you can certainly end up with foreground waits as it happens. But I suspect that with current hardware for most people, it's the sudden shift to a poor index access instead of a formerly efficient table scan that's going to do most of the performance clobbering.

                                But who cares, to be honest?

                                Because you need to note the provisos regarding[b] whether to rebuild the index in the first place, provisos which Don now signs up to as well: a totally quiet index during scheduled downtime. Fact is, you are unlikely to ever come across that situation, so we're now arguing about the number of angels that can dance on the head of a pin when in fact the probability is that neither the angels nor the pin actually exist in the first place.

                                Sure, if those circumstances did ever happen, a Sunday rebuild might cause performance troubles on Monday -for all sorts of reasons, block splits and leaf entry redistribution amongst them, changes in execution plans also amongst them, and maybe DMT extent growth issues also. I would trust that anyone noticing that would therefore decide not to repeat the experience on the following Sunday. I think it's OK to let DBAs make a monumental stuff-up once. You learn that way.

                                Pragmatically, Don, you, me, Tom... we are now all in functional agreement, I think: don't rebuild indexes unless no-one is around to care about it happening. I'd stop there, whilst the going was good, if I were you.
                                • 58. Re: When to rebuild indexes
                                  311441
                                  Hi Howard

                                  You say:

                                  "You also have to be using locally-managed tablespaces (because re-acquiring space a shrunken index decides it needs back in DMT is not funny) "

                                  and summarise

                                  "If your database is 24x7 or (more unlikely these days) still using DMT, then the original answer stands".

                                  I'm suggesting if you're using LMT or DMT makes little bearing whether the original answer still stands as potential index blocks splits as a result of a rebuild, especially if pctfree is set to a very low value, is a far greater issue than extent re-acquisitions.

                                  Therefore, an index regrowing is just as likely to be an issue in LMT as it is in DMT and therefore there are implicit dangers of simply rebuilding all indexes on a Sunday, regardless of whether the index tablespaces are LMT or DMT.

                                  Note it might be a memory operation, but it might require additional PIOs for the necessary blocks to be loaded in memory ...

                                  Do you have a link to where Tom Kyte has written about index extent re-acquisition being an issue after an index rebuild, I'd be interested in reading his thoughts on this ?

                                  Cheers

                                  Richard Foote
                                  • 59. Re: When to rebuild indexes
                                    ben23
                                    I wonder if anyone has published actual figures on the impact of massive index rebuilds?

                                    The last time I rebuilt about 90+% percent of indexes in an OLTP system, I did an exercise comparing figures before and after the rebuild from statpack. I could not see anything of statistical significance at all. I compared and graphed all the figures before and after the rebuild, including figures like block splits, extent growth, latch gets/misses/sleeps, cpu, logical I/O, physical I/O, buffer is pinned, buffer is not pinned and numerous others, and nothing stands out. More importantly, the phone did not ring any more than usual.

                                    To be fair, the rebuilds were intentionally spread over 5 business nights (using online rebuilds as this system was quiet at night) in case all the blocks decided to split the following day. PCTFREE were mostly 10%.

                                    No observable bleeps in system statistics were detected over the entire period.

                                    I don't do this as regular thing and I have no doubt there is an impact, I just wonder what that impact actually is.