Don Burleson: “Busting the Oracle Myth Busters”

If you followed the recent colourful debates on comp.databases.oracle.server on tuning using ratios vs. tuning using extended trace, sizing the buffer cache, calibrating the cost-based optimizer, and rebuilding indexes, read Cary Millsap's book on Optimising Oracle Performance, Niall Litchfield's Five Great Oracle Myths presentation, or read Richard Foote's paper on Index Internals, you'll be interested to know that Don Burleson has just published an interesting article for DBAZine entitled "Busting the Oracle Myth Busters", in which he looks at what he considers to be the new myths of Oracle performance tuning:

The first one addresses tuning using the data buffer hit ratio:

"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"

The next looks at whether performance benefits from regular index rebuilds:

"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 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).."

and the last looks at application tuning using extended SQL trace.

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

All interesting stuff, and the full article can be read over at DBAZine.