Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Busting the Oracle Myth Busters

Oracle Tips by Burleson Consulting

 

The Oracle database of the early 1990s is very different than 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 evidence 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 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 migrated/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 migrated/chained rows are a legitimate reason to reorganize the table, provided that we reset the PCTFREE parameter to prevent future chaining.

Old Myth: In 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 how 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 reread 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

Important 2009 update:  The rules for identification of candidates for index rebuilding are changing.  Please see my updated notes on index rebuilding.

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 required 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 (MOSC Note 77474.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 ad generate a huge dump which reveals all Oracle wait events for a session.

Now, don’t get me wrong; I have been reading 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, and replaced by the dbms_monitor package. 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.

Updates:

When can we "prove" a benefit from an index rebuild?  Here, Robin Schumacher proves that an index that is rebuilt in a larger tablespace will contain more index entries be block, and have a flatter structure:

"As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache."

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.“

This Kim Floss article shows the Oracle 10g segment advisor recommending a rebuild of an index: 

“The page lists all the segments (table, index, and so on) that constitute the object under review. The default view ("View Segments Recommended to Shrink") lists any segments that have free space you can reclaim.”


Oracle index rebuild advisor (Source: Oracle Corporation)

See my related notes on index rebuilding:

 


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.