ORACLE神话破灭

juniper

Emerging Oracle Myths


The mythology continues as Oracle professionals observe different database behaviors and reach inconsistent conclusions.




We also see some opinion leaders who are heavily promoted by Oracle Corporation, but who publish misleading statements about Oracle performance, thereby creating new Myths:



The consistent gets cannot, will not be affected by any of the optimizer_* settings.  they affect how the optimizer costs things, they have no effect on how things are actually processed.






Of course, changing the values of optimizer_mode, optimizer_index_cost_adj and optimizer_index_caching will change the optimizer’s decision about whether to do a full-scan or an index access execution plan, and this has a direct impact on the amount of consistent gets for any query.  


Types of Oracle Professional

Today there are two distinct groups of Oracle professionals, each with a very different view of Oracle tuning, and each accusing the other of perpetuating new Oracle Myths.  



The “Rules of Thumb” Myth – Many Oracle professionals believe that “rules-of-thumb” (ROT) are very dangerous, and note that if a ROT can be shown as invalid, even in a single artificial test, the ROT is not scientifically valid and therefore useless.

  

The “Script Kiddie” Myth – This myth says that running single-user SQL*Plus scripts to “prove” how Oracle will perform are almost always invalid under multi-user databases



Conclusion


This first part of my two part article is meant to lay the foundation for understanding Oracle mythology and show us how Oracle myths change over time.  In my next installment we will take a closer look at these widely-different approaches to Oracle technology and see how they can create confusion.

Yong Huang

最初由 juniper 发布
[B]Oracle Myths Revisited
SearchOracle

Mike Ault [/B]

juniper,

You probably didn't understand why I asked you to tell us the author and the article source. Although you did an excellent job at translating the article, you're apparently unaware, in an innocent way I'm afraid, that you may be violating the copyright law. Even worse, not revealing the original author may lead some readers to believe *you* wrote the article. Mike Ault, being a gentleman, may thank you for sharing the article with readers on this forum. But you should get permission from him first for translating it, and when you post your translation, clearly indicate the author and his written permission for you to do so. I'm fairly sure he'll be happy if you ask him first.

Yong Huang

juniper

Part 2
The New Oracle Myths


In this, our last installment we examine the nature of Oracle myths and understood how changing technology effects mythological status.




There are two schools of thought about “proving” Oracle solutions to performance problems.  



Today we see two “types” of Oracle Professionals, each saying that other Professionals are perpetuating new Oracle Myths.  In one group, the “Researcher” seeks “laws”, tautologies about Oracle behaviors that are true, while the Empiricist seeks “correlations” and deals in probabilities rather than absolutes.  






These mindsets have led to these current Oracle myths:



The “Rules of Thumb” Myth (sponsored by Research-oriented Professionals) – Many Research Professionals believe that “rules-of-thumb” (ROT) are very dangerous, and note that if an ROT can be shown as invalid, even in a single artificial test, the ROT is not scientifically valid.  Detractors note that simple, generalized “rules” form the basis for many of the Oracle 10g automation features, such as Automatic Memory Management (AMM) and the SQLTuning advisor.  

  

The “Script Kiddie” Myth (sponsored by Empirically-oriented Professionals) – This myth says that running single-user SQL*Plus scripts to “prove” how an Oracle change will behave in production are almost always invalid under multi-user database activity.  Detractors say that it is not necessary to run large-scale, multi-user benchmarks to “prove” how Oracle behaves.



So, are these myths?  Borrowing from Don Burleson’s hilarious DBAZine article “What type of DBA are you?”, we see several Oracle personality types.  In fairness, he created these deliberate generalizations to illustrate the mindsets of the Empirical and Scientific professionals.

juniper

The Empirical DBA



The Empirical DBA observes real-world situations, notes correlations and then generalizes their “rules” and applies their rules and observations to new databases.



The Empirical DBA believes that real-world Oracle databases with thousands of active users and hundreds of transactions per minute are often impossible to simulate and they do their testing with large, multi-user simulations.




Many in-the-trenches Oracle consultants report “phenomenon”, transient events that appear only under extreme system load and often cannot be reproduced.  The Empirical professionals utilize the heuristic approach to observe behavior and apply it to new situations.  



Heuristic 1. A rule of thumb, simplification, or educated guess that reduces or limits the search for solutions in domains that are difficult and poorly understood. Unlike algorithms, heuristics do not guarantee optimal, or even feasible, solutions and are often used with no theoretical guarantee.



The Empiricist DBA does testing with benchmarks, not test scripts.  Robert Freeman, a noted Oracle Press author and expert notes that the proofs proffered by scientists are often invalid:



Very few things in Oracle are black-and-white or simple and that is the fallacy of using a simple proof to prove anything. As complexity is increased, as layers are added, any number of factors can be introduced that change the conditions of the test, and thus totally invalidate it.



Does this mean the Empiricist blindly charges in making bold changes to clients databases without testing, checking statistics, waits, IO rates and timing and myriad other factors before applying heuristics? Only if they don’t want to get paid or new clients!



Now let’s examine the Research-oriented professional.

juniper

The Research DBA



The Research DBA has the motto is “Prove it,” and “Trust, but Verify”.  They love to perform research on their simple Oracle systems, creating conditions and proving the behavior of Oracle using reproducible, usually single-user, proof scripts.



The Research DBA believes that a database can be described with simple SQL*Plus test scripts and every assertion about database behavior can be proven with such research.  




The Research DBA does not believe in relying on “rules-of-thumb” and revels in finding exceptions to any general assertion about Oracle behavior.  Firmly-grounded in science, the Research DBA believes that the scientific method requires experimentation.



Mark Rittman notes that the Empirical DBA approach is problematic because it suggests that novices cannot become expert in Oracle tuning without many years of experience:



The other problem I have with the empirical approach is that it relies on many, many years of experience and observation of different types of Oracle database, and the "sixth sense" you therefore develop for coming up with performance tuning fixes, and therefore it's an approach that by definition cannot be taught to novices in any reasonable amount of time, and cannot be carried out by anyone other than very experienced DBAs.



Some authors are misleading their trusting followers with the mantra of “Prove it”, and they never note that “your mileage may vary”, especially for performance-related proofs.



Many times it seems the research DBA spends more time developing these “proofs” in seclusion than they do actually applying their techniques to real-world databases. Often when faced with real-world situations they will retreat into the mantra “Rebuild the entire application and call me in the morning…”, a response that while is probably true, doesn’t really help in the real-world where down time or lost time results in lost money.



In the real-world where I live, most database issues are code/application related. However, fixing the code is not, in most cases, a realistic option.



The Empirical DBA (as seen by the Research DBA)




The research DBA believes the Empirical DBA is lax and sloppy, and does not pay enough attention to details.



They make bold assertions about Oracle behavior that can be shown wrong under certain circumstances, yet they continue in their foolhardy ways, using “rules of thumb” and real-world observations instead of hard facts.




The Research DBA believes that the Empirical DBA is a “loose cannon” and cannot understand their impatience and disregard for elegant proofs and detail. Secretly, they think that the Empirical DBA is dangerous, and cringes at their propensity to rush into every database problem without supporting justification.



The Research DBA (as seen by the Empirical DBA)




The Empirical DBA sees little value in running limited, single-user SQL*Plus scripts and believes that the Research DBA is wasting their time “proving” minutiae that has no value in the real-world.



These “Script Kiddies” don’t test their hypothesis on large multi-user databases and they don’t understand how a high concurrency and load Oracle environment will change the results.




The Empirical DBA says that beginner Professionals are lulled into a false sense of security by running invalid “performance proofs”, using single-user instances of Oracle under small loads.  According to Don Burleson, relying on these artificial pseudo-proofs can spell disaster.  



“Many Oracle performance issues only manifest themselves under high systems load.  Time and time again I see cases where the DBA relied on bad advice from some limited SQL*Plus testing and assumed that their small-scale test was sufficient to prove the system’s speed and functionality.”



Now that we understand a bit about the mind-set of each group let’s look at how they accuse the other of perpetuating dangerous Oracle myths.

juniper

The “Rules-of-Thumb” Myth


Proponents of the “No Rules-of-thumb (heuristics) allowed” myth say that if any decision rule can be shown to be false, in even a single case, it is invalid.  They believe that the scientific method is the only way to effectively approach an Oracle performance problem, and all broad-brush tuning (changes to system-wide parameters, using faster hardware) are unscientific, non-elegant and offensive.



Empirical DBA? Might as well call them a "medium", because what you suggest here is merely "Database Meets Psychic Friends Network". Unless you analyze, test, analyze again (you know the drill) you aren't solving (or preventing) a problem.



The Empirical DBA Response


As Oracle technology becomes more available on personal computers, a movement has arose where “Oracle Research Scientists” adopted the mantra “Prove it” and started publishing SQL*Plus scripts as evidence of how Oracle actually behaves.  The heuristic approach has been codified inside the Oracle 10g advisory tools (Automatic Memory Manager, SQLTuning advisor, Oracle Data Mining), and heuristic techniques are well-proven and accepted within the scientific community.  



The Empirical professionals note that it is very difficult to document a transient performance issue, and impossible to set-up any repeatable “proof” of the behavior without disclosing the client’s schema and table data.  In sum, the Empirical DBA says that you often have to rely on end-user testimonials and “trust your source”.


The “Script Kiddie” Myth


The Empirical DBA will tell you the foundation of science is not in finding “proofs” of Oracle behavior, but in observing the real-world. With math you can prove that for a given gravity well, a feather and a lead ball will accelerate at the same rate toward the center of that gravity well. In the real world we know the affects of the surface area to weight and resistance will result in widely different rates of acceleration. The successive refinement of their heuristic rules form the experiential basis for “expert” Oracle tuning as noted in the book “Oracle Silver Bullets”.  Proponents of the “Script Kiddie” myth say that performing Oracle database testing in a single-user environment can lead to disaster.  



The Research DBA Response


The research DBA says that Oracle software behaves the same in all environments, and that their proofs offer valuable insights into the behavior of the Oracle software.  



The author of that book was smoking something funny.  You have my book -- you see when I say something,



I show all factors involved, I show the proof, I show the hard numbers and I make it so you can reproduce it



The single-user "proofs" are quite factual for what they are, but what they are (single-user, single CPU, single disk, no concurrent access) often does not describe real-world behavior.  This from Robin Schumacher, author of “Oracle Performance Troubleshooting”: http://www.rampant-books.com/book_2003_1_perf.htm



It's fine for DBAs to perform trials and postulate theories as long as they realize those theories may crash and burn in the real world.  Or, as someone well said a while back, "watch out when a beautiful theory meets a brutal gang of facts."



So, what do we make of this?  It’s clear that both groups believe in the value of testing and benchmarks, but they do it in very different ways.  



There is a big difference between a benchmark and a SQL*Plus “proof” and the real-world of high concurrent transactions makes most performance proofs useless.  For example, I published the results of my benchmark study on how Solid-state disks (SSD) are faster then traditional disks in a 135-page justification in the code depot of my book “Oracle Solid State Disk Tuning”.  An Oracle benchmark uses realistic data volumes and realistic user scenarios, proofs are a valuable tool.



Remember, unless luckily placed, a single shotgun pellet is rarely fatal, but the combined effect of the entire content of a shotgun shell is nearly always fatal. Volume does make a difference, be it users, data or both!






Back in the days before load testing software, we used to simulate system load by paying the staff to stay late in the evenings.  



I used a “cheerleader” bullhorn and I would get everyone to set-up the transaction, turn-on tracing, and then I would do a 3-2-1-“Go” countdown to get valid performance statistics.






Conclusion


Today we see the widespread use of load testing tools instead of SQL*Plus scripts, which allow you to simulate the effect of hundreds of people conducting a transaction at the same time. In fact, many tools let you take that single-user script and then multiple its affects many times, thus making it far more valuable in predicting real Oracle performance.



While single-user proofs validate how Oracle reacts in a single-user environment rely on real-world benchmarks whenever possible for decisions involving multi-user systems.

juniper

最初由 Yong Huang 发布
[B]

juniper,

You probably didn't understand why I asked you to tell us the author and the article source. Although you did an excellent job at translating the article, you're apparently unaware, in an innocent way I'm afraid, that you may be violating the copyright law. Even worse, not revealing the original author may lead some readers to believe *you* wrote the article. Mike Ault, being a gentleman, may thank you for sharing the article with readers on this forum. But you should get permission from him first for translating it, and when you post your translation, clearly indicate the author and his written permission for you to do so. I'm fairly sure he'll be happy if you ask him first.

Yong Huang [/B]

多谢提醒
这篇文章不是我翻译的,我在网上看到这篇中文的文章觉得不错,就转过来了,当时也没太注意作者

Fenng

有的时候给出一个连接更好

这种拷贝+粘贴会丢失文章本身的Link信息.同时文章的板式也乱得一团糟.有的时候还丢内容



而且,这篇文章其实并不好

juniper

最初由 Fenng 发布
[B]
而且,这篇文章其实并不好 [/B]

Fenng，可否略谈一二。
BTW，我觉得格瓦拉的头像更符合你的性格

Fenng

就这篇文章来说，

{{{{{索引和表不需要分开}}}}}  
我不知道有哪一位高手说过?  Mike Ault 写这篇文章很明显就是讽刺 Jonathan Lewis ,Howard J. Rogers 这几个家伙的，因为lewis 等非常喜欢构建一个很巧妙的例子来证明自己的观点．但这里Mike Ault  不知道是说谁,无地放矢


{{{{频繁更新的表和索引几乎不需要重新组织}}}}}
这个一直存在着争论，频繁更新的表如果重新组织的话，是会带来好处，但Don Burleson和Mike Ault 等从来不提及这样一个问题：频繁更新的表从业务上是否允许重新组织，索引Rebuild带来的影响如何避免 ? 如果这些都不考虑的话过分强调重新组织会带来好处，会有人相信么?

{{{{{多个块尺寸不会改善性能}}}}}
改善了性能，但是带来的管理复杂度呢 ?

Oracle公司大力推荐了一些提出观点的先进人物，但是他们却针对Oracle的性能公开了误导他人的言论

是的，的确有，Don Burleson 是个典型