 |
|
Techniques for Extracting Significant SQL for Tuning
Oracle Tips by
Burleson
|
Once we have tuned the initialization
parameters and established a baseline optimizer_mode, we are
ready to look into tuning individual SQL statements. Depending on our
environment, we have choices about where to find this SQL, and once it
is located, we have several options for observing the performance of
the SQL inside the shared pool.
Identifying High-Use SQL Statements
There are many ways to locate SQL statements. At
the highest level, you have the choice of locating the SQL source code
in several places:
-
Application programs Some Oracle
professionals know the location of their SQL source code and
interrogate the source code libraries to extract the SQL source
code.
-
The library cache The library cache
within the SGA will store the SQL source code, and it also provides
statistics about the number of executions. Most SQL tuning
professionals will use the rpt_sql_cache.ksh and
access.sql scripts for this purpose.
-
The stats$sql_summary table The
STATSPACK stats$sql_summary table stores the source for all
SQL statements that exceed the threshold values as defined in the
stats$statspack_parameter table. The STATSPACK tables are useful
because they keep a historical record of all of the important SQL.
Most SQL tuning professionals will use the SQL top-10 script
rpt_sql_STATSPACK.ksh for this purpose.
The details about executing these scripts were covered
in Chapter 7. Since the goal is to locate high-use SQL statements, the
library cache and the stats$sql_summary table are excellent
places to begin your quest for offensive SQL. For details on using
STATSPACK for SQL tuning, see Donald Keith Burleson, Oracle
High-Performance Tuning with STATSPACK (McGraw-Hill Professional
Publishing, 2001). For details on using ranking tools for extracting
SQL, see Chapter 7.
Extraction Scripts for SQL Statements
There are several techniques for extracting and
ranking SQL. Many people use third-party tools, but there are several
SQL*Plus scripts that can be used to locate important SQL statements.
Here are some of the common techniques:
-
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 rpt_sql_cache.sql.
-
Extract and analyze SQL from the library cache
using access.sql.
Please note that any of these techniques can be
used with either the historical STATSPACK stats$sql_summary
table or the v$sqlarea view. The columns in v$sqlarea
and stats$sql_summary are identical, so it is very easy to
write scripts that extract SQL from both locations.
When ranking SQL statements, we recognize that
it is more important to tune a frequently executed SQL statement for a
small performance gain than it is to tune a seldom-executed SQL
statement for a huge performance gain. In many cases, the Oracle
professional does not have the time to locate and tune all of the SQL
statements. It is also important to get an immediate measurable
result, so that management will continue to fund the tuning effort.
Hence, you must quickly locate those statements that are frequently
executed and tune these statements first.
At some point
you will encounter diminishing marginal returns for your tuning effort
(Figure 9-3). After you have tuned the frequently executed SQL and
move on to the less frequently executed SQL, you may find that the
time and effort for tuning will not result in a cost-effective
benefit.
Figure 3: The
diminishing marginal returns for SQL tuning
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
