The Oracle database of the early 1990s is very different from the database of the early 21st century. RAM was very expensive, disk was slow, and RAID was not yet popular for disk load balancing. As a direct consequence of these hardware limitations, the Oracle tuning rules that we used back in 1994 were very different than the rules we accept for Oracle tuning today.

Just last week I received a scathing note from a brand new Oracle DBA who found an article I published in 1993 offering Oracle7 tuning advice. This DBA complained that my decade-old advice was perpetuating an Oracle myth! I tried to explain to him that Oracle7 tuning was very different than today’s techniques, and besides, there are no hard and fast rules for Oracle tuning.

Fresh out of Graduate School, this DBA was unable (or unwilling) to accept that there are virtually NO concrete rules for Oracle tuning. Oracle tuning is dynamic and every tuning rule depends on the version of Oracle, the time of day you examine the database, the I/O-subsystem, and so on, ad infinitum. Even today, Oracle tuning remains very much an art form, as popularized by Christopher Lawson’s bestselling book, The Art and Science of Oracle Tuning.

Shifting Priorities

For example, over the past decade, I have witnessed a shift in Oracle database bottlenecks from I/O to CPU. This shift has radically changed the way we tune Oracle databases:

• 1994 — Most databases were I/O-bound because of the limited use of RAM memory for buffer caching and the inability of 32-bit servers to support large data buffers. Consequently, most Oracle tuning activities in 1993 dealt with minimizing physical disk I/O and involved using metrics such as the Data Buffer Hit Ratio (DBHR) to measure the probability that a data block will reside in the buffer for a subsequent access.
• 2004 — Due to falling RAM prices, Solid-state Disk (SSD), and the ability of 64-bit architectures to support huge data caches, many Oracle databases have shifted from being I/O-bound to being CPU-bound. Rather than focusing on minimizing physical disk I/O, Oracle 10g tuning focuses on reducing logical I/Os (consistent gets), often by tuning the SQL to reduce buffer touches and by addressing specific task wait events using the use Automated Session History (ASH) views. Of course, Oracle has recognized this shifting bottleneck as evidenced by the new CPU-based costing for SQL statements in Oracle10g. However, this shift toward CPU-based tuning DOES NOT mean that the ratio-based tuning of the early 1990s has become totally useless.

A Mythical Review

Some Oracle experts have pointed to errors in early Oracle documentation and accused Oracle Corporation of perpetuating “myths.”

The changes in Oracle tuning and monitoring techniques that have resulted from improvements to the hardware have lead to an overreaction among some overzealous Oracle professionals, who have created new Oracle myths while attempting to point out older Oracle myths (i.e., those concepts that were perfectly valid in 1994, but do not apply today). 

To understand this issue, let’s take a closer look at Oracle tuning mythology. Some of the Oracle “myths” from the early 1990s included these tautologies:

Old Myth: Tables perform best when in a single extent

This was incorrect in 1993 and is still untrue today, especially when using locally-managed tablespaces (LMTs) for tablespace storage. So, was this myth the deliberate work of a malicious DBA with a penchant for spreading bad advice?  Actually, there is some basis in historical fact for this myth.

The early releases of Oracle defaulted to a 4k blocksize, and tables with lots of extents often had chained rows, causing unnecessary disk I/O. We also had to worry about running out of table extents, before the maxextents=unlimited parameter was available. Today, we know that chained rows are a legitimate reason to reorganize the table, provided that we reset the PCTFREE parameter to prevent future chaining.

Old Myth: If the Data Buffer Hit Ratio (DBHR) falls below 90 percent, we must add more db_block_buffers

This was legitimate advice for many shops in the early 1990s when the 32-bit limitations (and expensive RAM) meant that many Oracle shops had too-small SGAs.

Even today, you should increase the db_cache_size if the data buffers are too small to cache the working set of frequently-referenced table and index blocks. This statement has become a “myth” because many shops are now using huge data buffers, far more than needed to cache a systems working set. Also, random-access data warehouses (using parallel full-table scans) don’t cache blocks in the data buffer, making this advice moot for large data warehouses.

In Oracle10g AMM, Oracle pools the v$db_cache_advice view and adjusts the size of each data buffer according to the marginal reduction of disk I/O as we add additional RAM (refer to figure 1).

Figure 1: An Oracle10g database with a too-small data buffer.

In their bold charge to condemn the old Oracle tuning techniques, many Oracle professionals have come full circle, dispelling the old techniques as “myths” while at the same time creating brand-new fallacies about Oracle performance.

Now that we have a historical understanding of traditional Oracle myths, let’s take a close look at some of these new Oracle myths:

The New Oracle Myths

As the underlying hardware technology changes, we must change our Oracle tuning techniques. However, there are still many Oracle databases that are still entrenched in 1990s' hardware technology, and the savvy Oracle tuning professional understands that the rules for Oracle tuning are heavily dependent on the external server and network environment.

Some of the younger Oracle professionals have noted that many of the guidelines from the early 1990s do not apply today and have falsely condemned the ancient tuning techniques of their DBA ancestors. This has led to a whole new set of Oracle myths, techniques that do not always apply. Let’s take a closer look.

New myth - Ratio-based Oracle tuning is meaningless

Some Oracle professionals point out that the DBHR can be manipulated at will (by running queries with high consistent gets), and falsely conclude that because the DBHR can be changed at will, the data buffer hit ratio is a meaningless metric!

Detractors of ratio-based tuning correctly point out that databases that seldom re-read data blocks (like a large data warehouse) and database systems with 30-gig data buffers that approach full-block caching will get little benefit from DBHR monitoring and tuning.

However, most OLTP systems and systems in which the working set of frequently-referenced data are not cached will greatly benefit from ratio-based tuning. Oracle Corporation recognizes that monitoring SGA usage ratios and adjusting the size of the SGA regions can have a dramatic impact on system performance, and this is the foundation of Oracle10g Automatic Memory Management (AMM) in which Oracle calculates the point of diminishing marginal return from adding data buffers (refer to figure 2).

Figure 2: The optimal size for a data buffer cache.

If we take a close look inside Oracle, we see that ratio-based buffer optimization is alive and well:

• Oracle9i ratio tuning tools — The introduction of the SGA advisory utilities including v$db_cache_advice, v$shared_pool_advice, and v$pga_target_advice. These utilities show the predicted marginal change to physical I/O from different sizes of the main SGA regions.
• Oracle10g ratio tuning tools — In Oracle10g, we see that the Oracle kernel has codified ratio-based tuning with the advent of Automatic Memory Management (AMM). The 10g AMM component uses data from the AWR dba_hist_db_cache_advice, dba_hist_pga_target_advice and dba_hist_shared_pool_advice views to continuously monitor the marginal benefits of changes to the shared pool, data cache size, and PGA region, dynamically morphing the region sizes with the goal of achieving the optimal size for the pool, based on ratio analysis (refer to figure 3).

Figure 3: A Ratio-based buffer recommendation from ADDM.

Oracle professionals now agree that ratio-based tuning is not a panacea, but it still has a legitimate place among the arsenal of Oracle tuning tools.

New Oracle Myth: Index rebuilding requires careful analysis

Many Oracle professionals have attacked the practice of rebuilding Oracle indexes on a regularly scheduled basis, correctly noting that the space reclamation and index performance does not always significantly change when an index is rebuilt. This has lead to the incorrect assertion that Oracle indexes are always optimized and never require rebuilding. Others attempt to create a set of index characteristics that accurately determine when an index will benefit from rebuilding.

In reality, many Oracle databases experience a huge benefit from periodic index rebuilding. Oracle recognized this benefit of index rebuilding when the Oracle9i online index rebuild feature made it possible to rebuild an Oracle index while the index is being updated (using the alter index xxx move online tablespace  yyy; syntax).

Like many Oracle consultants, I have seen cases in which performance radically improves after an index rebuild. This is especially true under these conditions:

1. When a index rebuild is combined with a table reorganization (using the dbms_redefinition package). This is especially useful when the data is accessed via index range scans and when the table is re-sequenced into index-key order (using single-table clusters, or via a CTAS with an order by clause).

2. When a heavily-updated index is rebuilt. In highly volatile databases in which table and column values change radically, periodic index rebuilds will reclaim index space and improve the performance of index range scans.

Detractors of frequent index rebuilding say that Oracle indexes are self-balancing, always optimal, and always fast. They discount Oracle’s own guidelines (MetaLink Note 77574.1) for when to rebuild an index, and claim that nobody has ever documented a clear-cut set of criteria to use to predict when an index should be rebuilt. However, the myopic detractors of index rebuilding forget several important points:

• Index rebuilds are low risk — Because the new index is created in temporary segments, Oracle will never destroy the old index until the new index has been created.
• Index rebuilds are unobtrusive — Oracle indexes can be rebuilt online without interruption to availability and DML activity.
• Index rebuilds are cheap — The cost of the duplicate disk to store a new index tree is negligible, as are the computing resources used during a rebuild. Many Oracle professionals forget that unused server resources can never be reclaimed, and servers depreciate so fast that the marginal cost of utilizing extra CPU and RAM are virtually zero.

In the real-world, Oracle professionals will occasionally schedule index rebuilds during off-hours, knowing that the worst possible scenario is that there is no space reclamation or performance improvements.

New Oracle Myth: Wait Event Analysis is the best way to tune Oracle

During the late 1990s, a small movement arose among Oracle professionals who advocated the analysis of 10046 trace dumps to locate Oracle performance problems. The Oracle details dumps are created with the “alter session set events '10046 trace name context forever, level 8'; command and generate a huge hex dump which reveals all Oracle wait events for a session.

Now, don’t get me wrong; I have been reading hex dumps since the 1970s, and I still remember how to use my slide rule and my old-fashioned E6B flight computer from my pilot flight school days. To this day, I try to get Oracle to publish their DSECTs, and I love to roll around a three-foot-thick, 80-lb. core dump on a dolly to impress visitors.

However, some of the wait event-tuning zealots wrongly forsake other Oracle performance methods. I once witnessed a wait event-tuning expert spend hours analyzing a dump, only to discover high physical write latency due to a RAID5 disk subsystem. A quick look at a STATSPACK report would have revealed this issue in just a few minutes.

Incidentally, the new Oracle10g Active Session History (ASH) component now automatically tracks all current wait events and interfaces with the Automated Workload Repository (AWR) to make the 10046 dumps largely obsolete. Here are the new 10g v$ tables for ASH (refer to figure 3), and there are exciting new ASH cluster DBA views for wait metrics including dba_hist_sys_time_model, dba_hist_sysmetric_history, dba_hist_sysmetric_summary, dba_hist_system_event, dba_hist_waitclassmet_history and dba_hist_waitstat.

Figure 4: The new 10g Active Session History views.

Keeping an Open Mind

So, what can we make of these myths?  The standard answer to almost every Oracle question is, “It depends.” Just as there are no firm rules about Oracle tuning, there are no real myths. Everything depends on something else, and Oracle tuning will always remain more of an art than a science.

Oracle tuning requires experience, intuition, and a willingness to consider whatever information and tools at your disposal. In Oracle10g, we now see that the AWE and the revamped Enterprise Manager (EM) promise to radically change the way that Oracle tuning is performed.

To be successful in Oracle tuning, we must keep an open mind and always be cognizant of the dynamic nature of all Oracle databases.

--

Donald K. Burleson is one of the world’s top Oracle Database experts with more than 20 years of full-time DBA experience. He specializes in creating database architectures for very large online databases and he has worked with some of the world’s most powerful and complex systems. A former Adjunct Professor, Don Burleson has written 15 books, published more than 100 articles in national magazines, serves as Editor-in-Chief of Oracle Internals and edits for Rampant TechPress. Don is a popular lecturer and teacher and is a frequent speaker at Oracle Openworld and other international database conferences. Don’s Web sites include DBA-Oracle, Remote-DBA, Oracle-training, remote support and remote DBA.