The SQL Tuning Process
Oracle Tips by Burleson Consulting
The process of tuning Oracle SQL is both
iterative and time-consuming. We begin by locating offensive SQL
statements either by “fishing” them from the library cache or by
extracting them from the stats$sql_summary table.
Next, we explain the SQL, tune each
statement, and make the changes permanent.
Note: If you tune your SQL by adding an index,
you can go backward in time and reexplain historical SQL in the
stats$sql_summary table. This technique will verify that the new
indexes have improved the execution plans of historical SQL.
There are several steps that are repeated
until all major SQL is tuned:
Locate Here we
locate offensive and high-impact SQL statements using STATSPACK
tables, the library cache, or the application source code.
we extract the offensive SQL syntax.
extracted SQL from step 2 is then passed to the Explain Plan utility
to get the execution plan.
Tune We then
tune the SQL with indexes, hints, and query rewrites. For each
change, we reevaluate the new execution plan and test the execution
time using the SQL*Plus set timing on command.
the tuning permanent by changing the SQL source program or by
creating a stored outline with the optimizer plan stability utility.
Of course, this is a highly simplified outline,
since each step can be quite complicated. Let’s take a closer look
at each of these steps.
Step 1: Identify High-Impact SQL
We begin our investigation into Oracle SQL
tuning by viewing the SQL that currently resides inside our library
cache. Many people ask where they should start when tuning Oracle
SQL. Tuning Oracle SQL is like a fishing expedition; you must first
“fish” in the Oracle library cache to extract SQL statements, and
rank the statements by their amounts of activity.
Oracle makes it quite easy to locate
frequently executed SQL statements. The SQL statements in the
v$sqlarea view are rank-ordered by several values. These are
presented in order, with the most important first.
executions The more frequently
executed SQL statements should be tuned first, since they will
have the greatest impact on overall performance.
disk_reads High disk reads
indicates a query that is causing lots of disk I/O.
rows_processed Queries that
process a large number of rows will have high I/O and may also
have an impact on the TEMP tablespace if sorting occurs.
buffer_gets High buffer gets may
indicate a resource-intensive query.
sorts Sorts can be a huge
slowdown, especially if the sorts are being done on disk in the
You can get lists of your SQL statements from
the v$sqlarea view or stats$sql_summary table in
descending order of any of these variables.
The executions column of the v$sqlarea
view and the stats$sql_summary table can be used to locate
the most frequently used SQL. When fishing for SQL, you can use a
third-party tool to display the SQL in the library cache or write
your own extraction script. The next section will cover three ways
to extract high-impact SQL:
Extract SQL from stats$sql_summary
with the STATSPACK SQL top-10 report (rpt_sql_STATSPACK.ksh).
Extract SQL from the v$sqlarea view
using the rpt_sql_cache.ksh script.
Extract and analyze SQL from the library
cache using access.sql.
Please note that either of these techniques
can be used with either the historical STATSPACK
stats$sql_summary table or with the v$sqlarea view. The
columns in v$sqlarea and stats$sql_summary are
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.