The term "myth" refers to a principle about Oracle behavior that 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 that 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.
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.
Myth: A data buffer hit ratio (DBHR) should be kept greater than 90%.
This myth was also propagated by Oracle in the early 1990s when almost all Oracle databases were I/O-bound and SGA size was constrained by 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 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 that can be run against your database to give you any DBHR you desire. Does this make it a myth? Oracle does not seem to think so, and ratio-based advisories form the foundation of the Oracle 10g Automatic Memory Management utility and the v$db_cache_advice advisory.
There are some current Oracle myths -- let's take a look at a few.
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.
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. 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 I/O rates and volumes for specific objects or types of objects and also allows for use of multiple-block sizes.
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 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.
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, Metalink 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.
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.
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.