 |
|
SQL Tuning as a
Phase of Oracle Tuning
Oracle Tips by Burleson Consulting |
The tuning of SQL is a very integral
component of Oracle tuning, and one that can have an immediate
impact on performance. However, it is very tempting to dive into SQL
tuning without performing the prerequisite tuning activities.
There is a
very specific order to Oracle tuning. All Oracle database are
organized as a hierarchy, with global components affecting
subcomponents, as you can see in Figure 1-5.
Figure 5: The Oracle tuning hierarchy
This hierarchy is very important because any
bad tuning at a high level will affect all activities at the level
beneath it. Here are the details about the Oracle tuning hierarchy:
- Environmental tuning
Environmental tuning involves the tuning of the database server,
the network, and the disk I/O subsystem. Only after these
components are tuned can the Oracle Remote DBA begin tuning the Oracle
database.
- Database server tuning
The tuning of the Oracle database server is a prerequisite for
all Oracle tuning. If the database server experiences shortages of
RAM or CPU, no amount of Oracle tuning will remedy the problem.
- Network tuning The
network substrate must be tuned to ensure that there are no packet
shipping issues at the network protocol level. For details
on tuning Oracle network communications, see Oracle
High-performance Tuning with STATSPACK, Chapter 7.
- Disk tuning The tuning of
the disk I/O subsystem is an absolute prerequisite to Oracle
tuning. Disk I/O bottlenecks and disk access problems must be
addressed before Oracle tuning begins.
- Instance tuning Once the
external environment is tuned, the next phase is the tuning of the
Oracle instance. This involves tuning the SGA memory region and
tuning the behavior of the Oracle background processes. With
regards to SQL tuning, this is the step where the default
optimizer_mode for all SQL in the database is set. For
details about Oracles optimizer_mode parameter, see
Chapters 14 and 15.
- Object tuning Once the
instance is tuned, each Oracle object must be tuned for optimal
performance. This phase involves getting the proper setting for
all storage parameters, especially those storage parameters that
affect I/O. The settings for pctfree, pctused, and freelists all
have an important impact on SQL performance.
- SQL tuning Once all of
the general tuning has been accomplished, you are ready to embark
on SQL tuning. This phase involves the identification of high-use
SQL statements, tuning the statements, and ensuring that the
optimal execution plan is made permanent.
Database Design and SQL
Performance
Note that we
have deliberately left out one of the most important factors in SQL
speed, the design of the original data structures, outlined in
Figure 1-6. The amount of data normalization and the amount of
planned redundancy within the Oracle tables will have a dramatic
impact on the speed of the queries. As you will learn later in this
text, by adding redundant columns to tables (denormalization), you
can avoid expensive SQL joins and improve performance.
Figure 6: The relative performance benefits of
different tuning activities
We have
omitted this discussion because it is rarely possible for the Oracle
Remote DBA to change the table design once the application has gone into a
production environment. However, we will be discussing column
replication techniques in a later chapter, to show you how you can
avoid a SQL table join by replicating a data column from one table
to another (as shown in Figure 1-7), and how you can provide a
trigger to keep the replicated data item up to date.
Figure 7: Improved performance with column
replication
Next, let’s look at the challenges that are
faced by the Oracle Remote DBA when undertaking SQL tuning.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
