Tuning with Optimizer
Oracle Tips by
This chapter discusses the use of optimizer
plan stability in Oracle8i and shows how you can improve the
run-time performance of SQL statements and also provide a easy method
to permanently change the execution plans for SQL statements. This
chapter will cover the following topics:
Introduction to stored outlines
Preparing Oracle for stored outlines
How to create and modify a stored outline
Managing a stored outline
Introduction to Stored Outlines
The optimizer plan stability feature of Oracle8i
has been a long time coming. The earlier databases such as DB2 and
IDMS have had the ability to store execution plans since the 1980s,
and the concept of stored SQL outlines has had widespread acceptance
in the non-Oracle world for decades.
The argument behind optimizer plan stability is
that there exists only one optimal execution plan for any SQL
statement, and once located, the execution plan should never change,
even when the CBO statistics or initialization parameters are changed.
Of course, this philosophy is contrary to the basic tenet of Oracle
cost-based optimization, which expects SQL statements to change
execution plans when the characteristics of the table and index
statistics change, or when a change is made to an important
initialization parameter such as sort_area_size or
Regardless of philosophy, creating a stable
execution plan for Oracle SQL has two major benefits:
Faster run-time execution
The SQL does not have to be reparsed and a new execution plan does
not have to be developed when a SQL statement is executed. This
greatly improves the preprocessing for SQL statements, and this
benefit is especially evident in decision support systems where
thousands of different SQL statements can be generated.
Permanent SQL tuning changes
Once tuned, optimizer plan stability allows for SQL statements to
always have the same execution plan. There will be no surprises when
a change is made to an important initialization parameter such as
sort_area_size or when the CBO statistics change.
TIP: Stored outlines are great for tuning
SQL in database application suites where the SQL source is not
available or cannot be changed. For example, SAP and PeopleSoft
applications products can now have SQL tuning without touching the
Now that we see the benefits of using stored
outlines, let’s take a look at how optimizer plan stability works.
When a SQL statement enters Oracle8i, the database will perform
the following actions (Figure 13-1).
Check shared pool Hash the SQL statement and see if an
identical statement is ready to go in the shared pool. If it is found,
reexecute the SQL statement from the shared pool.
Check stored outlines If the SQL is not found in the
shared pool, check for a stored outline in DBA_OUTLINES view in the
OUTLINE tablespace. If a stored outline is found, load it into the
shared pool and begin execution.
Start from scratch If nothing for the SQL statement is
found in the shared pool or stored outlines, parse the SQL, develop an
execution plan, and begin execution.
Figure 1: Executing a
new SQL statement
Optimizer plan stability can also aid us in our
SQL tuning effort. Oracle provides a package called outln_pkg
that allows you to manage your stored outlines and Oracle also
provides an option for the ALTER SYSTEM and ALTER SESSION commands for
the automatic collections of stored outlines for all of your SQL
statements. However, it is not a good idea to turn-on the option to
automatically store outlines unless you are prepared to examine each
stored outline and ensure that the best execution plan is being used.
If you have already tuned your SQL, you can
alter system set_stored_outlines=true and Oracle will
define plans for all issued SQL statements in all sessions at the time
they are executed; these stored plans will be reused until altered or
dropped. If you have not tuned your SQL, you can create individual
stored outlines for your session by entering the alter session
Just as the shared pool will not reuse a SQL
statement unless it is identical to a previous statement, the
effective use of stored outlines depends on the statement being
reissued in an identical fashion each time it is used. If even one
space is out of place, the stored outline is not reused. Whenever
possible, all SQL should be stored in a PL/SQL procedures, functions,
or packages, and bind variables should always be used to ensure that
the SQL is reusable. This allows reuse of the stored image of the SQL
as well as reuse of your stored outlines.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.