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:
|
|
and include the URL for the page.
