Locate the High-Use SQL
Oracle Tips by Burleson Consulting
The first step is to locate the most
frequently executed SQL statements. Since SQL may enter Oracle from
a variety of sources (Pro*C programs, Visual Basic code, etc.), the
Oracle Remote DBA must prepare an approach to sample the SQL that is
currently in the library cache. There are two approaches to locating
candidate SQL statement for tuning:
Use STATSPACK The STATSPACK
approach involves using the stats$sql_summary table to
capture SQL statements.
Fish through the library cache
This approach involves using utilities to explain all of the SQL
that is currently in the library cache.
Let’s take a close look at each of these approaches.
Using the STATPACK to Capture SQL
When using STATSPACK, you will find that the
stats$sql_summary table is the most highly populated of all
of the STATSPACK tables. If your threshold values are set very low
and you have a busy database, it's not uncommon to get several
hundred rows added the stats$sql_summary table each and every
time STATSPACK requests a snapshot. Hence, it is very important that
the Remote DBA remove unwanted rows from the stats$sql_summary table
once they are no longer used for SQL tuning. Remember though, that
the SQL stored in the stats$sql_summary table is filtered by
the thresholds stored in the stats$statspack_parameter table.
This is the number of executions of the SQL statement
disk_reads_th This is the
number of disk reads performed by the SQL statement (default
parse_calls_th This is
the number of parse calls performed by the SQL statement (default
buffer_gets_th This is
the number of buffer gets performed by the SQL statement (default
Remember, a SQL statement will be included in the
stats$sql_summary table if any one of the thresholds is
exceeded. Most Oracle Remote DBAs will schedule an hourly STATSPACK sample
so that they will get a sample of all of the SQL that resides in the
library cache at the time that the snapshot was gathered.
The major drawback to using STATSPACK for
collecting SQL statements is the need to adjust the threshold values
as your SQL tuning progresses. As you identify and tune SQL
statements, you will need to lower the threshold values so that you
can continue to tune the the less frequently executed SQL
statements. Lowering the threshold causes more rows to be placed
into the stats$sql_summary table and will cause the STATSPACK
tablespace to fill rapidly. Most Oracle Remote DBAs will periodically
delete the stats$sql_summary rows once they have tuned those
Fishing Through the Library Cache
Another popular approach to SQL tuning is to
randomly extract SQL statements from the library cache. These are
several techniques that are used to do this:
Using a third-party tool
Third-party GUI tools can be used to quickly display the SQL in
the library cache and extract the most frequently executed and
resource-intensive statements. These tools include Oracle’s
Enterprise Manager Performance Pack, SQL*Lab, Q Diagnostic Center,
SQL Expert, and many others.
Using SQL*Plus scripts We will
also explore a script called access.sql that will extract and
explain all of the SQL that is currently in the library cache. We
will go into great detail on access.sql in Chapter 9.
Once we have located the candidates for
tuning, we proceed to individually tune each SQL statement.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.