The Steps to Oracle SQL Tuning
Oracle Tips by Burleson Consulting
Tuning individual SQL statements is one of the
most time consuming and challenging areas of Oracle tuning. SQL
tuning can also be quite tedious because of the complexity of some
SQL statements, and a complex SQL statement can often take many
hours to tune.
If your database has thousands of complex SQL
statements, this tuning can take many months. At a high level,
Oracle SQL tuning involves the following activities:
- Ensure that all prerequisite tuning has been
done on the server, disk, network, instance, and tables.
- Rank the SQL statements in your library
cache to identify the statements that will result in the most
benefit from SQL tuning (i.e., those with the highest number of
- Tune the SQL statement by adding hints,
rewriting the query, or adding or changing indexes.
- Make the SQL execution plan persistent by
updating the SQL source code, or by using optimizer plan
- Repeat steps 2 through 4 until all SQL has
been located and tuned.
There is some debate about when SQL tuning
should be performed. Some experts believe that SQL tuning can be
conducted first, before the server, disk, network, instance, and
objects are tuned. In fact, this sequence is tested in the Oracle
Certified Professional (OCP) exam. However, you must remember that
there are dependencies between a SQL statement and its environment.
With regards to the external environment, bottlenecks at the server
disk or network level can skew the performance of SQL executions,
thereby making it very difficult to tune the statement. The same
issue applies to instance tuning. If we have not tuned the instance,
resetting Oracle initialization parameters could undo the execution
plans for SQL that has already been tuned. For example, changing the
value of db_file_multiblock_read_count could change the
behavior of the CBO, causing hundreds of SQL statements to change
their execution plans.
Goals of SQL Tuning
Oracle SQL tuning is a phenomenally complex
subject, and entire books have been devoted to the nuances of Oracle
SQL tuning. However there are some general guidelines that every
Oracle Remote DBA follows in order to improve the performance of their
systems. The goals of SQL tuning are simple:
Remove unnecessary large-table full-table
scans Unnecessary full-table scans cause a huge amount
of unnecessary I/O and can drag down an entire database. The
tuning expert first evaluates the SQL in terms of the number of
rows returned by the query. If the query returns less than 40
percent of the table rows in a row-resequenced table, or 7 percent
of the rows in an unordered table, the query can be tuned to use
an index in lieu of the full-table scan. The most common tuning
for unnecessary full-table scans is adding indexes. Standard
B-tree indexes can be added to tables, and bitmapped and
function-based indexes can also eliminate full-table scans. The
decision about removing a full-table scan should be based on a
careful examination of the I/O costs of the index scan versus the
costs of the full-table scan, factoring in the multi-block reads
and possible parallel execution. In some cases an unnecessary
full-table scan can be forced to use an index by adding an index
hint to the SQL
Cache small-table full-table scans
In cases where a full-table scan is the fastest access method,
the tuning professional should ensure that a dedicated data buffer
is available for the rows. In Oracle7, you can issue cache
commands, and in Oracle8 and beyond, the small table can be cached
by forcing it into the KEEP pool.
table xxx cache; -- Oracle7
alter table xxx storage (buffer_pool keep); -- Oracle8
Verify optimal index usage This
is especially important for improving the speed of queries. Oracle
sometimes has a choice of indexes, and the tuning professional
must examine each index and ensure that Oracle is using the proper
index. This also includes the use of bitmapped and function-based
Verify optimal JOIN techniques
Some queries will perform faster with NESTED LOOP joins, others
with HASH joins, and others with MERGE or STAR joins.
Review Subqueries Every
correlated and non-correlated subquery should be examined to
determine if the SQL query could be rewritten as a simple table
These goals may seem deceptively simple, but
these tasks compose 90 percent of SQL tuning, and they don’t require
a through understanding of the internals of Oracle SQL.
Now that you understand the goals of SQL
tuning, let’s take a look at the steps of tuning. We will revisit
many of these steps in detail in later chapters, but for now you
need to understand the basic steps.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.