1 3 4 5 6 7 Previous Next 163 Replies Latest reply: Oct 22, 2009 5:23 PM by Hoek Go to original post RSS
      • 60. Re: Index rebuild
        706417
        +"Many years ago, in our parliament, a member called the opposition members, idiots."+

        Ah! The sad case of the improperly placed comma.


        Love - Don Lewis
        • 61. Re: Index rebuild
          635471
          Don Lewis wrote:
          But! With the best will in the world, none outside of Oracle does fully understand the internals. It's not open source, is it? If I see a problem that looks like an internals issue, guess what? I raise an SR. We pay enough for it.
          But what you can't do is raise an SR in which you ask whether you should rebuild your indexes regularly, even if you also provide details on table, indexes, access patterns, updates, inserts etc.. You might get refered to a Metalink note, or to their own consultancy service, but these issues are much bigger than simply reading the source code and coming up with a recommendation.

          In fact, test cases are invaluable in this situation not because of the result that you see when someone else runs them on a particular system configuration but because you can use their scripts and your own input to find out whether a phenomenon that you observe on your system is explainable by particular configuration items (eg. freelist groups) and whether they are fixed by them. With that result in hand you learn whether a simple change can prevent the (alleged) need to rebuild indexes regularly for example. Maybe you also find a bug or quirk that you can feed back to Oracle Support as a bonus.

          Test cases are highly useful for getting quality support as well, of course. Without one you have almost nothing to offer the support staff for them to work with.
          • 62. Re: Index rebuild
            706417
            But what you can't do is raise an SR in which you ask whether you should rebuild your indexes regularly, even if you also provide details on table, indexes, access patterns, updates, inserts etc.. You might get referred to a Metalink note, or to their own consultancy service, but these issues are much bigger than simply reading the source code and coming up with a recommendation.

            I disagree. I can raise an SR about pretty much anything, especially if my indexes are evidently becoming a bottleneck as the week progresses, week after week.

            I have 2 possible avenues to wander down:
            1. Spend an age trying to troubleshoot - with a distinct possibility of having to contact Oracle Support.

            2. Contact Oracle Support.

            It's just software. Spending ages fiddling about for little gain is not sensible. If it's not working as expected, then, as long as the Support is paid for, I get Support onto the case.

            If I had lots of lovely time to delve, fiddle, furtle and fetish all the issues in the databases I help look after, then, sure, it'd be very jolly, very interesting and very rewarding. But I don't have time because I work for a real business with real SLAs and real managers who kick bottoms if they think someone is on some sort of ego-boosting voyage of discovery.

            Which raises an interesting aside: what do most DBAs do for a living? Fix stuff or manage stuff?

            Cheers - Don Lewis
            • 63. Re: Index rebuild
              635471
              Don Lewis wrote:
              But what you can't do is raise an SR in which you ask whether you should rebuild your indexes regularly, even if you also provide details on table, indexes, access patterns, updates, inserts etc.. You might get referred to a Metalink note, or to their own consultancy service, but these issues are much bigger than simply reading the source code and coming up with a recommendation.

              I disagree. I can raise an SR about pretty much anything, especially if my indexes are evidently becoming a bottleneck as the week progresses, week after week.

              I have 2 possible avenues to wander down:
              1. Spend an age trying to troubleshoot - with a distinct possibility of having to contact Oracle Support.

              2. Contact Oracle Support.

              It's just software. Spending ages fiddling about for little gain is not sensible. If it's not working as expected, then, as long as the Support is paid for, I get Support onto the case.

              If I had lots of lovely time to delve, fiddle, furtle and fetish all the issues in the databases I help look after, then, sure, it'd be very jolly, very interesting and very rewarding. But I don't have time because I work for a real business with real SLAs and real managers who kick bottoms if they think someone is on some sort of ego-boosting voyage of discovery.

              Which raises an interesting aside: what do most DBAs do for a living? Fix stuff or manage stuff?

              Cheers - Don Lewis
              If you don't have the time to understand it by reading a few articles and running some test cases then you have no choice but to raise an SR of course. Really, it's the application developers who should be understanding this index stuff and using their knowledge of the application and the RDBMS software to configure indexes and write code correctly. I agree at least partly with you --- by the time the issue has fallen to the production DBA's then punting it to Oracle Support may be the only choice they have based on their own workload.

              I disagree that Oracle Support are going to do anything meaningful about it -- about some issues certainly, but not about index growth patterns. That's a consultancy role unless you can gather the evidence to support the view that there is a bug or a serious deficiency in the code.

              Or course you could bring in external consultants, but who would you choose? A company with an official attitude that they don't have to try to understand the difficult internals and they'll just raise an SR on your behalf, or a specialist who does understand what questions to ask, what tests to run, and what to change to fix the issue?
              • 64. Re: Index rebuild
                Uwehesse-Oracle
                It seems to me that you are mostly making fun of all people in this forum trying to help others with a technical (or call it scientific) approach.

                If your solution for any technical problem is: Call oracle support! so be it. But I wonder what you are doing then in a technical forum that is designed to help people with technical problems.

                This is not about correcting grammar or quotations of others in the first place, you know?
                Also, this is not made for joking about others (in the first place, at least :-) )

                Kind regards
                Uwe

                http://uhesse.wordpress.com
                • 65. Re: Index rebuild
                  706417
                  There isn't (as far as I know) any consultancy that simply raises SRs! If I wanted ace expert advice, sure, I'd definitely consider wheeling-in a guru. But that does not mean that I have to do the legwork. Not to the level of peeling away the layers of the onion, so to speak. That's where I draw the line.

                  Some folk make a living from peeling onions; I don't.

                  All I am driving at is that there are, of course, major limitations to any external-to-Oracle person's knowlege of the product and that attempting to reveal hidden truths, etc., is, in the main, a potential waste of effort and dollars. The product changes regularly. It's got loads of bugs. It's just software. It's not physics and it's not science and it's not anything other than human-created code and that means illogical stuff will be hidden throughout.

                  I am not advocating an SR-only approach. But once the "Oh! We've made an error; that's the cause!" stage has passed, then the vendor gets a prod!

                  Love - Don Lewis
                  • 66. Re: Index rebuild
                    108476
                    Hi David,
                    I think that this would make a great corporate philosophy for a consultancy business
                    Glad you agree.

                    It's ridiculous to reverse engineer Oracle with test cases, when we can ask the people who hold the source code.
                    We don't try to understand the internals. We just open SR's
                    Most working DBA's have no time to doink around with the internals, it's a waste of valuable time.

                    Perosnally, I find it to be a waste of time, because everhting will change in the next relese anyway . . . .

                    That's why we pay for Oracle technical support.

                    If I want to know the internals of something, why mess with shots in the dark?

                    Oracle tech support people can call-up the source code and tell us exactly how something works . . .
                    • 67. Re: Index rebuild
                      108476
                      Hi Jonathan,
                      You might wish to make a few changes to the note on your website
                      Have you been reading my responses?

                      I thought that I made it clear that I’m not exactly your greatest fan, why on earth would you think I would want your feedback?

                      Wait a minute. . . This “gift” is an olive branch, right?

                      This is your way of apologizing, isn't it?

                      Aw, that’s kinda sweet . . . .

                      OK, I accept your free tech edit, and I have updated the page per your suggestions.

                      I also added some details on dbms_repair, rebuild_freelists . . .
                      • 68. Re: Index rebuild
                        635471
                        burleson wrote:
                        Hi David,
                        I think that this would make a great corporate philosophy for a consultancy business
                        Glad you agree.
                        I guess I'm unclear on why a company would pay an external consultancy with no affiliation to Oracle Corporation to raise SR's on their behalf. Not much of a value-add ...
                        Most working DBA's have no time to soink around with the internals.

                        Perosnally, I find it to be a waste ofg time, because everhting will change in the next relese anyway . . . .
                        Best to leave the understanding of the internals to others then, I'd say. I do worry that with everything changing in every release, all your previous experience becomes immediately irrelevant. That must be a worry. How do you find out what experience of yours has become irrelevant if you do not have a test case to find out what has changed?
                        • 69. Re: Index rebuild
                          546494
                          Hi Richard,
                          coalescing indexes, interesting. I have just started to look at this today, to start to evaluate its possibilities.

                          will let you know what I find

                          regards

                          Alan

                          ps. like your taste in music. I saw Pink Floyd perform 'the wall' in London back in the late 70s.
                          • 70. Re: Index rebuild
                            108476
                            Hi Marcin,
                            You started writing about religion not Jonathan.
                            No, sorry Jonathan did.

                            Please re-read the quote.

                            Maybe you are not familiar with Richard Dawkins?
                            Quote is a quote and it can be anything.
                            No, not at all!

                            This is a very famous quote, and if you read it in context, the meaning is crystal clear.

                            It says that people who believe in God are gullible fools.
                            So testing of software is wasting of time ?
                            A test case is not the same as software testing!

                            The problem is that a single-user "test case" on a PC is not a valid test, by any measure. . .

                            It does not accurately reproduce real-world behavior, especially in performance tuning, where slowdowns are only seen under heavy loads.

                            Read what Oracle says about software testing, test cases are not even mentioned:

                            http://wiki.oracle.com/page/Database+Benchmarking

                            Nothing beats real-world empirical observations.
                            most of Jonathan test cases (especially in his book) are very well documented
                            I don't know about this book, but I disagree in principle.

                            There are over 400 init.ora parameters that influence system behaviors, not to mention other factors like object parms and CBO stats.

                            Like I said, test cases are worse than meaningless . . .
                            Ask air lines to stop booking a tickets and rebuild indexes in their databases
                            Good example!

                            I provide Oracle support for two major airlines!

                            Why should they have to stop booking flights to perform index maintenance?
                            Last time when one of that kind of system was out of service - it was huge delay on most of European airports.
                            You bet! Their downtime costs are well over $100,000 per minute . . .

                            Managing a 24x7 database is like working on a car while it flies down the freeway at 80 MPH!

                            It's not easy, but it can be done, if you know what you are doing!
                            I know that you have university background and I can't understand why you are fighting with a test cases and models
                            Good question!

                            Back in the 1980's I was way more into theory and modeling than I am today.

                            Why? Because it's too time consuming, especially when you have a real database to use instead!

                            I did extensive research with simulation modeling and operations research.

                            I know how it works, and I'm here to tell you, it's NOTHING like a test case!

                            Creating a valid simulation of real-world index behavior would require at least a full week of set-up work, plus a good simulation software package.

                            I wrote a book on the topic of Oracle simulation modeling:

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

                            However, I do believe that we can use empirical techniques to develop a script to accurate detect when indexes benefit from rebuilding.

                            Alan says he uses one . . .

                            See, that's "real" science. Test and re-test, using your real-world workload.

                            Just look at how Oracle is embracing "silver bullet" testing in 11g RAT.

                            They don't use test cases, they use real-world SQL workloads, and they have a framework to test test the effect of "holistic" tuning, silver bullets like CBO stats and init.ora parms, where a single change has a profound impact on the entire landscape of database performance.
                            most of new science is base on models.
                            Yeah, but Oracle is NOT a science!  Oracle is just a software program!

                            The idea of an "Oracle scientist" is kinda strange to me . .

                            What's next, an MS-Excel scientist?
                            • 71. Re: Index rebuild
                              635471
                              burleson wrote:
                              Read what Oracle says about software testing, test cases are not even mentioned:

                              http://wiki.oracle.com/page/Database+Benchmarking

                              Nothing beats real-world empirical observations.
                              http://wiki.oracle.com/page/FAQ
                              5. Does Oracle endorse or validate any content you see here, or any content it links to?
                              No. This site is sponsored by Oracle but belongs to the community.
                              • 72. Re: Index rebuild
                                108476
                                a specialist who does understand what questions to ask, what tests to run, and what to change to fix the issue?
                                If I caught a consultant charging a client for time running artificial test cases, I would fire them and report them to the BBB . . .

                                I recently helped a client recover thousands of dollars that a consultancy billed them for doing useless "research" . . .

                                They literally changed a client tbhousands of dollars doing a root cause analysis, when all that was needed was a single change to optimize an init.ora parameter.

                                If you think that running test cases helps you learn, then by all means, go for it.

                                But do it on your own time.
                                • 73. Re: Index rebuild
                                  108476
                                  Hi Uwe,
                                  This is not about correcting grammar or quotations of others in the first place, you know?
                                  Good point.  Let's get back on track!

                                  Let’s summarize what we have covered so far:

                                  - Properly executed during scheduled downtime, index rebuilding/coalescing is a zero-cost, zero-risk activity.

                                  - For databases without scheduled maintenance windows, it is necessary to write scripts to detect indexes that may benefit from a rebuild. This involves examining the index trees as well as historical SQL activity.

                                  - An index will benefit from a rebuild/coalesce if it experiences a significant number of full scan operations (index fast-full scans, index range scans). You can write queries against AWR (dba_hist_sql_plan) of STATSPACK (stats$sql_plan) to see how often an index experiences multi-block scan activity.

                                  - By definition, a freshly rebuilt index is in it’s pristine state. When a DBA rebuilds an index, they will examine historical workloads and adjust PCTFREE to accommodate future DML activity.

                                  Now, we are waiting and hoping that Alan will share his script which accurately detects when indexes need rebuilding . . . .

                                  Also, let's address the issue of the SQL workload . . .

                                  Kim Floss has already noited on OTN that the OEM segment adbvisor detects indexes that benefit (space wise) from rebuilding:

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

                                  "“The page lists all the segments (table, index, and so on) that constitute the object under review. The default view ("View Segments Recommended to Shrink") lists any segments that have free space you can reclaim.”

                                  If we want to write a script to detect when indexes benefit from a schedule rebnuild/coalesce, we must also examine the historical SQL and only rebuild when the index gets "significant" range scan and full-scan activity.

                                  I have a script here that I use to track index usage:

                                  http://www.remote-dba.net/oracle_10g_tuning/t_oracle_index_usage_tracking.htm

                                  This script could easily be modified to identify indexes with "significant" range scan and full-scan activity.

                                  But what constitutes "significant" scan activity? One oer hour? 100 per hour?

                                  In a database without a scheduled downtime windows (where there is a real cost to index rebuilding), how do we determine the cost/benefit threshold?
                                  • 74. Re: Index rebuild
                                    108476
                                    Hi Alan,

                                    Is it possible to share your index rebuild detection script?

                                    Why reinvent the wheel?
                                    1 3 4 5 6 7 Previous Next