This discussion is archived
1 4 5 6 7 8 11 Previous Next 163 Replies Latest reply: Oct 22, 2009 3:23 PM by Hoek Go to original post RSS
  • 75. Re: Index rebuild
    706417 Explorer
    Currently Being Moderated
    Hi Don,

    Might be worth a peep at Richard Foote's blog - there's a (to me quite revealing) article about indexes actually getting bigger after a rebuild, not getting better.

    Someone's recently asked him if there's a way of stopping a rebuilt index from being bigger than it was before the rebuild. Interesting stuff.


    Regards - Don Lewis
  • 76. Re: Index rebuild
    MarcinP Oracle ACE
    Currently Being Moderated
    >

    Hi Don,

    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.
    What a minute - a test case is a part of software testing - http://en.wikipedia.org/wiki/Software_testing

    If you are going to compute a TPC - sure we can't use a PC but what if we want to reproduce or test a algorithm ?


    >
    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 . . .
    Maybe you should read ;)

    >
    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?
    According to that Oracle doc

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm#i1006652

    Online index rebuild should be performed in low activity time and can't be done using parallel mode
    - sorry but I can't imagine online rebuilding of index for 100 mln+ tables (60 GB) in online mode - it take too long.

    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!
    80 MPH - it's a piece of cake in Europe ;)
    Try German highway and 120 MPH ;)

    >
    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!
    Ok I agree that this is time consuming but if we trying to solve issue which occur many times maybe this is wise way to do it.

    I want to ask one question - how you learn a new things ? If someone ask you to upgrade to Oracle 12g (13 or more) and it will be your first installation
    how you solve that problem ?

    >
    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 . . .
    To develop a script you should to know what parameters/options/statistics you should take into consideration
    because as you said there is more then 400 Oracle parameters - I can't believe that ONE script can solve all problems for ALL Oracle instalation
    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.
    You are talking about performance and I agree and I thing that all people agree that this is a best solution for measure a performance changes.
    But how you know that to change before you start tests. Is a brute force solution only one way to solve everything ?


    regards,
    Marcin Przepiorowski
  • 77. Re: Index rebuild
    706417 Explorer
    Currently Being Moderated
    "- sorry but I can't imagine online rebuilding of index for 100 mln+ tables (60 GB) in online mode - it take too long"

    On your PC, maybe, not on my system. And did you mean 100 million rows?
  • 78. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:

    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?
    Because the truth is the truth, no matter what you think about the person who supplies it.

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

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 79. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:
    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.
    Since you insist that index rebuilds are 100% risk free and zero cost, can we assume that you don't charge your clients for doing them, but simply start a job to do it automatically ?

    Re: Can DBA work at home?

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

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    
    Extraordinary claims require extraordinary evidence
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 80. Re: Index rebuild
    MarcinP Oracle ACE
    Currently Being Moderated
    Don Lewis wrote:
    "- sorry but I can't imagine online rebuilding of index for 100 mln+ tables (60 GB) in online mode - it take too long"

    On your PC, maybe, not on my system. And did you mean 100 million rows?
    No, not on my PC.
    2 node RAC 4 CPU (2 x 4 core) with EMC CX3-80
    Daily partition load - more than 100 000 000 rows


    regards,
    Marcin
  • 81. Re: Index rebuild
    706417 Explorer
    Currently Being Moderated
    You load 100M rows but maintain the indexes?
  • 82. Re: Index rebuild
    MarcinP Oracle ACE
    Currently Being Moderated
    Don Lewis wrote:
    You load 100M rows but maintain the indexes?
    A bulk load is about 100M after that normal DML activity is started.
    First plan was to rebuild index after bulk load in online mode to allow a DML operation just after buld load
    but it took too long and whole process has to be redesign and parallel index rebuild was a good choice.

    That I want to point here that ONLINE index rebuild is much slower than OFFLINE and
    if maintenance window is small we have to choose index to rebuild very careful and choose only these which really need it.

    But to do that in my opinion we have to know why we have to rebuild index - and AFAIK this is clue of Jonathan tests and research.

    regards,
    Marcin
  • 83. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Try German highway and 120 MPH
    Ah, I love the autobahn!

    I miss Germany, I've been wanting to take my son to Ockotoberfest . . . but he's too busy studying in grad school . . . 
    I want to ask one question - how you learn a new things ?
    I learn new things like most scientists, by observing real-world systems, and reading stuff by people who I trust!

    I've been a full-time, working DBA for over 25 years now, and I've never needed to write an artificial test case.  Not once.

    It baffles me why any practicng DBA would want to write a test case, when they have a real-world test database, full of real data and waiting to be used . . .
    there is more then 400 Oracle parameters - I can't believe that ONE script can solve all problems for ALL Oracle instalation
    Or describe performance. . . .

    I'm sure that people can learn hoew Oracle works by writing test casdes, and that's fine . . .

    Where test cases fail is in my area of extertise, Oracle tuning!

    Alan has his progream yto detect indexes that need rebuilding, and I have mine too!

    I'll bet that neither is 100% perfect, but that does not mean, that with enough experinentaytion and adjustment of the salient variables (index key size, block size, SQL workload characteristics, PCTFREE, &c) that a generalized script cannot be developed, a script that could be used by all DBAs, in all circumstances!

    Oracle Corporation is working on this problem for us. In an OracleWorld 2003 presentation titled “Oracle Database 10g: The Self-Managing Database” by Sushil Kumar of Oracle Corporation, Kumar states that the new Automatic Maintenance Tasks (AMT) Oracle10g feature will "automatically detect and re-build sub-optimal indexes.
  • 84. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Hi Marcin,
    That I want to point here that ONLINE index rebuild is much slower than OFFLINE
    Yes, it's a direct function of the level of DML happening during the rebuild!

    I try to schedule them during times of low DML activity.
    we have to choose index to rebuild very careful and choose only these which really need it.
    Perfect!

    That's how everybody with a 24 x 7 database does it, they write scipts to "chose" a list of indexes . . .

    Marcin, what criteria do you use to find your own index rebuild/coalesce candidates?

    And Don Lewis and others too, what criteria do you use?

    I'm guessing that Alan has an NDA to prevent him from sharing his program, and I can't post mine either . . .

    But that does not mean that we cannot disclose some general rules . . .
  • 85. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Since you insist that index rebuilds are 100% risk free and zero cost, can we assume that you don't charge your clients for doing them,
    It's unprofessional and unethical to charge people for the time spent running jobs!

    I'm offended that you would even ask me such a question.

    I've helped in forensics cases where consultants tried to charge for this abuse, cheating their customers for watching jobs run, while they diddled around with longops scripts and test cases in order to justify their theft. . . .

    It's almost as bad as consultants who charge their clients for learning stuff that they should have already known . . .

    **************************************************************
    but simply start a job to do it automatically ?
    No, we start the jobs automatically too . . .

    It's called "automation", Jonathan, you should check it out!

    ANYTHING that is well-structured and repetitive can be automated, it's the very foundation of decsion support systems:

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

    You would be amazed at what can be automated these days . . . .
  • 86. Re: Index rebuild
    635471 Expert
    Currently Being Moderated
    burleson wrote:
    I learn new things like most scientists, by observing real-world systems, and reading stuff by people who I trust!
    I don't think that many scientists would recognise this description of "most scientists". You've missed out several steps, ones that they teach to children: http://www.sciencebuddies.org/mentoring/project_scientific_method.shtml

    Lookit: "Do Background Research" ... "Test Your Hypothesis by Doing an Experiment" ... "Communicate Your Results". Those are what test cases are used for.
    Oracle Corporation is working on this problem for us. In an OracleWorld 2003 presentation titled “Oracle Database 10g: The Self-Managing Database” by Sushil Kumar of Oracle Corporation, Kumar states that the new Automatic Maintenance Tasks (AMT) Oracle10g feature will "automatically detect and re-build sub-optimal indexes.
    And did they deliver a functionality to automatically detect and re-build sub-optimal indexes as an AMT? I think not. They predefined the Automatic Statistics Collection Job and the Automatic Segment Advisor Job (and in 11g there is the Automatic SQL Tuning Advisor), and my own best practice is to disable (or at least severely modify) the Automatic Statistics Collection Job immediately because it's a menace to an important database.

    So six years after this got a mention in a presentation it is still vapourware. Nothing.
  • 87. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Because the truth is the truth, no matter what you think about the person who supplies it.
    Not when it's presented by somebody with ulterior motives, somebody who knows how to alter parms and settings to bias the results . . .

    You can spot these because the settings make no sense.

    It would be like somebody "proving" how PGA works, but they turn-on the MTS on a tiny PC, just to rig the results.

    Test cases in the hands of somebody with a bone to pick become a weapon of abuse . . .

    That's why personal honesty and integrity is so important.

    Once integrity is compromised, trust is gone, regardless of any "proof scripts".
  • 88. Re: Index rebuild
    635471 Expert
    Currently Being Moderated
    burleson wrote:
    That's why personal honesty and integrity is so important.

    Once integrity is compromised, trust is gone, regardless of any "proof scripts".
    Maybe you should credit Jonathan with correcting the mistakes in your article, hmmm?

    http://www.remote-dba.cc/oracle_tips_freelist_blocks.htm
  • 89. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Maybe you should credit Jonathan with correcting the mistakes in your article
    Maybe because there were NO mistakes in the article?

    Look for yourself, his suggestions were all trivial things, nothing that suibstantially changed the meaning of the article . . .

    All of his changes were addressed by a superflous edit of a few sentences . . .

    The only reason I addreseed them was in hopes that he might contribute something to this index rebuilding dicsussion, which, as you can see, he has not. . . .

    Lewis posts in this thread are all over the place, way too much subtrefuge for my taste, too little on-point communication . . .

    Even when asked direct questions about index rebuilding, he misses them, and instead responds with something out of left field, like his critique of an unrelated article . . .
    Once integrity is compromised, trust is gone . . .
    David, speaking as a practicing data warehouse engineer, why is it that people in the computer industry like to use grandiose titles that they did not earn?

    - This SQL Server guy lists his title as "Genius Laureate of the United States of America":

    http://www.timothygoodwin.com/titles.htm

    He is also "One of the Greatest Minds of the 21st Century - For Remarkable Achievements In The Field Of Computer Information Systems As Declared By The Governing Board Of Editors Of The American Biographical Institute"

    - People without any credientials in enginering call themselves software engineers.
    I know a fellow who calls himself an "Oracle engineer" and he barely gradiated from high school.

    - Oracle people label themselves as world renowned "experts", while not disclosing their credientials and experience.

    - This computer guy won the "Noble Prize for outstanding contributions to humanity":

    http://www.horosoft.net/system_approach.htm

    In case you are not familiar with it, the Noble Prize is the rednecks answer to the Nobel prize:

    "The United Cultural Convention, North Caroline, USA, sealed his nomination for receipt of the 2002 Noble Prize for Outstanding achievement and Contributions to Humanity."

    I have more examples here:

    http://dba-oracle.blogspot.com/2006/10/are-you-man-of-year.html
1 4 5 6 7 8 11 Previous Next

Legend

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