Oracle Myths and Legends

by Mike Ault

The term Oracle ?myth? refers to a principle about Oracle behavior which either was never true, or, used to be true, but is no longer. Most Oracle myths originated as the result of changing technology. 

Most people agree that many of today's Oracle Myths were perfectly valid during their day (e.g. ?disk load balancing is critical to performance?), but they became mythological as hardware and Oracle software improved.

 

 

Let's not forget that Oracle technology is more than 15 years-old, and the technology of 1989 is far different than it is today. 

Fortunately, most Oracle professionals fully-understand the changing dynamics of Oracle Mythology and how once-valid advice can become invalid and take-on mythological status.   
 

Ancient Oracle Myths

There are many old Oracle techniques which were very useful in the past but have become myths as the technology has changed.   Confounding the problem are the thousands of Oracle shops that are running on prehistoric hardware and unsupported releases of Oracle software.

Let's take a look at some of the older myths. 
 

Ancient Myth: Objects perform better in a single extent

 

Oracle University courses in the early 1990's stressed that the compress=y export option would greatly improve the performance of resulting tables.  Today, Locally-Managed Tablespaces (LMT) makes this advice invalid. 
 

Ancient Myth: A data buffer hit ratio (DBHR) should be kept greater than 90%

 

This myth was also propagated by Oracle Corporation in the early 1990's when almost all Oracle databases were I/O-bound and SGA size was constrained by the 32-bit server technology.  Oracle-based products such as SAP also note in their manuals that the DBHR should be over 90%.  Oracle author Robert Freeman notes:

 

It has been demonstrated on many occasions that it is easy in a basic proof to prove just about anything. Given a basic proof I can prove that the buffer cache hit ratio means nothing, or I can prove that it is the most important thing in the world.

 

I know of several scripts which can be run against your database to give you any DBHR you desire. Does this make a myth?  Oracle does not seem to think so, and ratio-based advisories form the foundation of the Oracle10g Automatic Memory Management utility and the v$db_cache_advice advisory.

 

But there are some current Oracle myths, let's take a look at them.

 

Current Oracle Myths

 

The current Oracle myth debates are largely a result of the changing Oracle technology and the inability of some Oracle professionals to adapt to the changes.

 

Current Myth: Indexes and tables do not need to be separated

 

This myth arose because of recommendations by Oracle back in the early 1990's when disk contention was a major issue and the "separation" myth is misunderstood. 

It wasn't too long ago that separation of indexes and tables in databases was a good and accepted method for improving performance.   Of course this was because otherwise they would be on the same disk platter if they weren't separate and would conflict.  Moving indexes to a tablespace on a separate disk from tables always improved performance, not just the separation into a separate tablespace.

 

The main argument, supported by 10046 traces with a single-user system is that access to tables and indexes in a single query is not asynchronous in nature, but is rather a linear process. However, even in single user systems this fails to take into consideration the required head movement and disk latencies associated with reading index, then table. In a multi-user environment it fails to take into consideration all of the above plus the effects of multi-user access against co-located tables and indexes.

 

Now with properly laid-out RAID much of the contention issues of co-location are removed or mitigated, however, maintenance is still made easier with separation into several tablespaces for tables and indexes. Separation into discrete tablespaces allows tracking of IO rates and volumes for specific objects or types of objects and also allows for use of multiple-block sizes.

 

Current Myth: High-update tables and indexes rarely need reorganization

 

This myth was started by the statements of Oracle experts that claimed that Oracle indexes always remain balanced and that indexes rarely benefit from rebuilding. Below we see the suggestion that, somehow, understanding ?why? table and indexes become fragmented might help:

 

unless you want to be caught in the infinite loop of org, reorg, org, reorg.... You better have a clue as to "why"

 

While in a perfect world you could rebuild once using the absolute correct parameters and never have to rebuild again, I am afraid this doesn't happen in the real world. It's rather like expecting to clean your house once when it is full of rowdy teenagers, it just doesn't make sense.

 

Today, it is well-understood that tables and indexes with high concurrent insert, update and delete activity system can quickly get a sub-optimal structure and require reorganization to reduce I/O for multi-block scan operations (using Oracle's dbms_redefinition package, alter index move/rebuild, alter index coalesce, or even alter table move depending on availability requirements.) The concept of index balance is two-pronged, while a B-Tree is always height balanced, it can become sparse or right-handed, so it becomes width or load unbalanced.  
 

Current Myth: Multiple blocksizes don't improve performance

 

This myth was perpetuated because multiple blocksizes were originally designed to support transportable tablespaces and some people could not see the other important side-benefits of multiple blocksizes. The chief benefit of different blocksizes is the more efficient use of limited RAM regions (db_cache_size, db_32k_cache_size, etc.) and the intelligent segregation of objects to reduce logical I/O (consistent gets) for multi-block scan reads. 

 

Today, MOSC notes that the multiple blocksize parameters are among the most important in Oracle tuning, and noted experts such as Robin Schumacher has demonstrated that Oracle indexes will build more-optimal b-tree structures within a large blocksize. Also, Reorganizing a high-DML index, or using small blocksizes for random single-row fetches (index access unique) of small rows can reduce the size in db_cache_size and therefore reduce PIO because more blocks fit into the cache area.

 

For example, some attempt to prove the assertion using small, artificial single-user experiments and suggest that multiple blocksizes are unlikely to help in a real-world database.  However, real-world shops report a very different experience with multiple blocksizes and a 32k blocksize for indexes:

 

"My favorite recent article was on 32KB indexes - our client (200GB+) saw a 20% reduction in I/O from this simple change...  ?- Steve Taylor, Technical Services Manager EMEA

 

So, here we see how changing technology can convert a perfectly-valid approach from 15 years ago into a ?Myth?, and reaching a false conclusion from a single-user test-script can create a modern myth, again because of changing technology.

 

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.  

 

Part 2 - The New Oracle Myths

Mike Ault

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.

 

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.

 

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.

 

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:

 

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.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

---