Oracle tuning has always been the most difficult area of Oracle
database management. Because of the flexibility of Oracle, the
internal software is phenomenally complex and there are many
hundreds of causes of Oracle performance problems. The inherent
complexity of the Oracle database leaves many shops unable to
certify that their database is properly tuned.
In addition to the complex nature of the Oracle
software, we also have the issue of the dynamic nature of Oracle
applications. An Oracle database is constantly changing, and it is
never exactly the same at any two times. Because of the dynamic nature
of tuning an Oracle environment, it is very difficult for the Oracle
database administrator to get a handle on what's going on inside their
database. Many people who've attempted to tune an Oracle database say
that it's analogous to attempting to work on a car while it is flying
down the highway at 60 miles per hour!
While it may be true that the Oracle databases
are in a constant state of flux, there is a general approach that most
Oracle experts use when tuning the Oracle database. It's very
important to take a top-down approach to tuning Oracle databases, such
that you start at a very high level, taking a look at the overall
Oracle environment and then successively drill down into more detail
as you begin to tune the individual components within the database
engine. For Oracle, the top-down approach means starting with the
server, drilling down to the instance, drilling down to the objects,
and finally examining the Oracle SQL.
The goal of this text is to give you the
STATSPACK tools and diagnostic techniques that are required to ensure
that your Oracle database is performing at an optimal level. While you
may not become an Oracle tuning expert from reading this book, you
will have a high-level understanding of the important Oracle tuning
issues and know how to run STATSPACK queries to get performance
The Overall Tuning Approach
While there is no silver bullet for tuning
Oracle databases, a comprehensive approach to Oracle tuning can help
ensure that all of the bases are covered, and that no important tuning
facts have been overlooked. In tuning an Oracle database, you have to
start by taking a broad look at the external environment and
successively drill down for more details (see Figure 1-1).
Figure 1: The Oracle database tuning
The concept of using a drill-down technique is
very important to Oracle tuning. We must start at a very broad level,
examining the overall environment and looking carefully at the
database server for any problems that might exist within CPU, RAM, or
disk configurations, as shown in Figure 1-2. No amount of tuning is
going to help in an Oracle database when the Oracle database server is
short on resources.
Figure 2: Tuning the Oracle environment
Once we've completed the tuning of the Oracle
server environment, we can then begin to take a look at the global
parameters that affect the Oracle database (the Oracle instance). When
looking at the Oracle database, we take a look at the database as a
whole, and we pay careful attention to the Oracle initialization
parameters that govern the configuration of the SGA and the overall
behavior of the database, as shown in Figure 1-3.
Figure 3: Tuning the Oracle instance
Once the database server in the Oracle instance
has been tuned, we can then begin the work of taking a look at
individual Oracle tables and indexes within the database. At this
phase, we take a look at the storage settings that can govern the
behavior of a table and take a look at how well the settings
accommodate the processing needs of the individual objects (see Figure
Figure 4: Tuning Oracle objects
Once the Oracle objects are tuned, we then move
into tuning the individual SQL queries that are issued against the
Oracle database. This is one of the most challenging of all of the
areas of Oracle tuning because there can be many thousands of SQL
statements issued against a highly active Oracle database. The task
for the person tuning the Oracle SQL is to identify those SQL
statements that are used most frequently and apply the tools necessary
in order to tune each statement for the optimal execution plan (see
Figure 1-5). We will also explore static binding and show how to
improve execution by keeping bind plans for the SQL.
Figure 5: Tuning Oracle SQL
In summary, Oracle tuning involves the
following steps, with each step being more general and broad than the
step beneath it:
Server, network, and disk tuning If
there is a problem with the Oracle server, such as an overloaded CPU,
excessive memory swapping, or disk I/O bottleneck, then no amount of
tuning within the Oracle databases is going to improve your
performance. Hence, the first thing the Oracle professional examines
is the server, disk, and network environment.
Instance tuning The
Oracle SGA is tuned, and all of the Oracle initialization parameters
are reviewed to ensure that the database has been properly configured.
This phase of Oracle tuning is directed at looking for resource
shortages in the db_cache_size, shared_pool_size, and
pga_aggregate_target. We also investigate important default
parameters for Oracle such as optimizer_mode.
Object tuning This
phase of tuning looks at the setting for Oracle tables and indexes.
Settings such as pctfree, pctused, and freelists
can have a dramatic impact on Oracle performance, and each object can
benefit from proper storage settings.
SQL tuning This
is the most time-consuming tuning operation because there can be many
thousands of individual SQL statements that access the Oracle
database. At a high level, we identify the most common SQL statements
and tune each statement by carefully reviewing the execution plan for
the SQL and adjusting the execution plan using Oracle hints. We will
also be investigating the new optimizer plan stability feature.
Optimizer plan stability allows improved performance by storing a
ready-to-go execution plan for SQL statements. We will also see how to
implement optimizer plan stability with the OUTLINE package, so that
we can modify execution plans for specific SQL statements. This is
especially useful in cases where you are using vendor-supplied SQL and
you cannot change the SQL source code. For example, in Oracle
Applications and SAP, you are not allowed to change the SQL, but with
optimizer plan stability you can tune the SQL by changing the stored
execution plan for the SQL.
Design tuning The
design of the application is the single most important factor in
Oracle performance. Unfortunately, most Oracle administrators are
unable to change a poor design, either because they are using
proprietary software or because the design is already implemented in
It is critical to the success of your Oracle
tuning effort to follow the tuning steps in their proper order. Many
neophyte Oracle Remote DBAs will immediately begin to tune SQL statements
without considering the environment in which the SQL is running.
Mistakes like these can often cause problems with the overall tuning
effort because the broader tuning issues have not yet been identified
While later chapters will explore each of these
areas in great detail, let's begin by covering the major areas so that
we can understand their impact on Oracle performance.