Tune the SQL
Oracle Tips by Burleson Consulting
The process of
tuning the SQL statement involves several activities. SQL tuning
moves from a global level to a specific one. The goal of SQL tuning
is to verify that the execution plan is optimal for the statement.
Verifying the speed to SQL execution is normally done by using
SQL*Plus with the set timing on command and actually timing
the speed of the query.
Changing the optimizer mode You
can try changing the optimizer mode to rule, all_rows, or
first_rows. This will normally result in several execution
plans, and each must be timed to determine the plan with the
fastest execution time.
Adding indexes You can add
indexes (especially bitmapped and function-based indexes) to
remove unwarranted full-table scans. However, be aware that adding
an index can change the execution plan for many other SQL
statements. It is not uncommon to add an index only to find that
the speed of other SQL statements changes.
Adding hints You can force a
change to the execution plan by adding hints to the select
statement. Oracle provides dozens of hints to change the execution
plan for SQL, and we will be going into detail on hint-based SQL
tuning in Chapter 12.
Make the Tuning Permanent
Once tuned, it is
critical that the tuning change become permanent. There are several
methods for doing so. Making a SQL tuning change permanent is
especially important in an environment where global changes such as
changing the optimizer_mode initialization parameter or
adding indexes could potentially change the execution plans for many
Optimizer Plan Stability
provides a new package called OUTLINE that allows the Remote DBA to store a
ready-to-run execution plan for any SQL statement. This utility has
- Parsing and execution time is reduced
because Oracle will quickly grab and execute the stored outline
for the SQL.
- Tuning of SQL statements can easily be made
permanent without locating the source code.
- SQL from third-party products (e.g., SAP,
Peoplesoft) can be tuned without touching the SQL source code.
stability enables you to maintain the same execution plans for the
same SQL statements, regardless of changes to the database such as
reanalyzing tables; adding or deleting data; modifying a table's
columns, constraints, or indexes; changing the system configuration;
or even upgrading to a new version of the optimizer.
To use optimizer
plan stability, you must run the dbmsol.sql script from $ORACLE_HOME/rdbms/admin.
When the script is executed, a new Oracle user called OUTLN is
created (with Remote DBA privileges) and a package called OUTLN_PKG is
installed to provide procedures used for managing stored outlines.
Oracle provides the
CREATE OUTLINE statement to create a stored outline. The stored
outline contains a set of attributes that the optimizer uses to
create an execution plan. Stored outlines can also be created
automatically by setting the initialization parameter
create_stored_outlines=true. For more details on using stored
outlines, see your Oracle8i documentation and Chapter 13.
Change the SQL Source
environments, the SQL source code must be located in order to make
the tuning change permanent. This can be a challenging problem for
an application where the SQL is distributed in client-side
applications, systems that use ODBC to communicate with Oracle, and
systems that generate dynamic SQL.
In practice, the
Oracle Remote DBA should strongly advocate removing all SQL from
application programs. This is normally achieved by placing all SQL
inside stored procedures, and then placing the stored procedures
inside Oracle packages. This approach makes the SQL source easy to
locate because it is in the Oracle data dictionary. It also has the
side benefit of making all remote applications portable, because all
calls to Oracle are encapsulated into function and stored procedure
Before we get into
the details of this SQL tuning text, let’s review some of the tools
that we will be using in this book. In order to make this book
useful for everyone, we are going to use standard Oracle utilities
and scripts for all of our SQL analysis. This alleviates the need
for expensive third-party products and ensures that every reader can
analyze and tune all SQL.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.