Don Burleson: “Busting the Oracle Myth Busters”
August 31st, 2004 by Mark Rittman
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.
