Oracle: Swapping stored outlines
Articles by Rampant author Chris Foot
Vendor Oracle Systems
for details on this technique.
Also see: this article on
stored outlines, and s
Some of the information in this article is
also covered in my 10g blog on DBAzine.com. But I have
currently become such a strong proponent of Optimizer Plan
Stability that I thought it was important to give a little more
in-depth coverage to this feature.
Optimizer Plan Stability allows us to tune
SQL that we canít change. That includes tuning a third-party
software vendorís SQL without getting the vendor involved.
Before Optimizer Plan Stability, when an
administrator identified the canned applicationís poorly
performing SQL, the third-party vendor was contacted to change
their software. The vendor changed the code and delivered the
updated software to the customer. After the customer tested the
software to make sure the change didnít affect the applicationís
functionality, the new code was implemented in the production
environment to affect the tuning change. Anyone who has
experience with third-party application vendors knows that this
is often a time-consuming (if not impossible) process.
However, by using Optimizer Plan Stability,
administrators are able to capture the statementís access path
and use OEM or SQL update statements to apply hints to influence
the optimizer to create a more optimal execution plan.
Oracle preserves the execution plans in
objects called ďStored Outlines.Ē You can create a Stored
Outline for one or more SQL statements and group Stored Outlines
into categories. Grouping Stored Outlines allows you to control
which category of outlines Oracle uses. As a result, you can
toggle back and forth between multiple outline categories and,
therefore, multiple access paths for the same statement. If the
SQL text of the incoming statement matches the SQL text in a
Stored Outline in that category, Oracle considers both texts
identical and uses the outline. Oracle considers any differences
to be a mismatch.
But it seems that the matching process
isnít as strict as the hard-parse vs. soft-parse match that is
performed during statement execution. Before a statement enters
the parse phase, Oracle matches the statement being executed to
statements that are already parsed and stored in the shared
pool. When Oracle finds a matching statement in the shared pool,
it will do a soft parse on the SQL statement. If Oracle does not
find any matching SQL in the shared pool, it will perform a hard
parse, which requires that more steps be performed than a soft
parse. In this, as with almost everything else, fewer steps =
faster performance. During a soft parse, you bypass the step of
creating the access path, so one way to reduce the number of
optimizations performed is to perform soft parses.
The key to obtaining a high number of soft
parses is to match as many incoming statements to statements
already stored in the shared pool as possible. This is trickier
than it sounds and requires vigilant and rigorous SQL coding
standards. When Oracle looks for a matching SQL statement in the
shared pool, the statements must match exactly. That is, the
statement must match in case, line breaks, and spacing. If the
SQL doesnít match exactly, Oracle executes a hard parse.
I was interested to learn whether or not
the SQL matching process for Stored Outlines was just as strict
as for soft parses. I experimented with capitalizing some of the
characters in the Stored Outline SQL and executing the same
statement in SQLPLUS in all lower case, and it still used the
outline. Good news! I then placed hits of ENTER in the SQL I was
executing in SQLPLUS and, once again, the outline was used. I
finished my testing by using a combination of line breaks, extra
spaces, lower- and upper-case, and the outline was still used.
But if I rearranged any text in the statement, or if I did not
use bind variables, Oracle considered this to be a mismatch, and
it did not use the Stored Outline. If the SQL statement you want
to use to create the Stored Outline never changes its search
criteria, I would expect that you would be able to use the
statement without using bind variables.
Oracle documentation states that
administrators are able to activate cursor sharing if bind
variables are not used to get the SQL statements running in the
database to match the SQL in the Stored Outline. Setting the
parameter CURSOR_SHARING to SIMILAR or EXACT tells Oracle to
replace all hard-coded values in the SQL statementís WHERE
clause with bind variables. I have used this parameter to reduce
hard parses in a large online transaction-processing database
and it certainly works. Oracle replaced every statementís
hard-coded values with bind variables. I mean, every
statement. I noticed that it was also transforming OEMís
SQL used to retrieve database performance statistics.
So, statements that used to look like this
SELECT FIRST_NAME, LAST_NAME, DEPT_ID FROM
WHERE LAST_NAME = 'SMITH'
look like this when you set the parameter
CURSOR_SHARING = SIMILAR:
SELECT FIRST_NAME, LAST_NAME, DEPT_ID FROM
WHERE LAST_NAME = :V1
Only once did activating cursor sharing
cause the optimizer to choose less-than-optimal access paths; we
were then forced to use Stored Outlines to reduce the number of
statements being optimized. As stated previously, using soft
parses to reduce the number of SQL statements sent to the
optimizer is much more efficient than freezing their access
paths with Stored Outlines, but using Stored Outlines is
certainly a viable backup plan. I recommend that you use soft
parses first, then fall back to Stored Outlines if the primary
Optimizer plan stability allows
administrators to use OEMís Stored Outline Editor or SQL
UPDATE statements to influence the optimizer to use a more
high-performance access path, which is once again frozen in the
If you want to learn how to use SQL UPDATE
statements to change a statementís frozen access path, go to
METALINK.ORACLE.COM and search using the keywords ďplan
stabilityĒ to find detailed instructions in Document Number
144194.1 (it should be the first document that pops up in the
To create Stored Outlines, use the CREATE
OR REPLACE OUTLINE statement to create an outline containing a
single SQL statement. Administrators can freeze multiple SQL
statementsí access paths in a Stored Outline by executing the
Log in to SQLPLUS.
Issue the ALTER SESSION SET
CREATE_STORED_OUTLINES =TRUE statement to freeze access paths in
the DEFAULT category or ALTER SESSION SET CREATE_STORED_OUTLINES
= user defined category name statement to freeze access
paths in a user-defined category name.
Run one or more SQL statements
that must have their access paths frozen.
Execute ALTER SESSION SET
CREATE_STORED_OUTLINES = FALSE to deactivate the storage of
To use Stored Outlines when Oracle compiles
a SQL statement, set the system parameter USE_STORED_OUTLINES to
TRUE or to a user-defined category name. You can also set this
parameter at the session level. If you set USE_STORED_OUTLINES
to TRUE, Oracle uses outlines in the DEFAULT category; if you
specify a category name with the USE_STORED_OUTLINES parameter,
Oracle uses outlines in that category until you re-set the
USE_STORED_OUTLINES parameter to another category name or until
you suspend outline use by setting USE_STORED_OUTLINES to FALSE.
And if you specify a category name, and Oracle does not find an
outline in that category that matches the SQL statement, Oracle
searches for an outline in the DEFAULT category.
Remember that you are trying to get the SQL
being executed to use a more efficient access path. In our case,
the vendor was optimizing the entire database for high-volume
transactions that required sub-second response times. To achieve
quick response times, the vendor set OPTIMIZER_MODE to
FIRST_ROWS, OPTIMIZER_INDEX_CACHING to 90 and
OPTIMIZER_INDEX_COST_ADJ to 20 ó all excellent settings if you
want to influence the optimizer to use NESTED LOOP joins and
index access paths to improve online transaction performance.
However, our problem was that the
application also required the execution of large batch jobs that
joined large tables and scanned a large percentage of the tables
being accessed. Needless to say, the parameter changes made by
the vendor to achieve sub-second OLTP response time didnít allow
the optimizer to create the most optimal access paths for
heavy-batch processing. We realized that the only way we could
get the vendorís batch jobs to run in the desired timeframes was
to influence some of the access paths to use more HASH joins and
table scans (which are typically more suited to processing large
volumes of data).
We achieved this by using the ALTER SESSION
commands to customize our session parameter settings in SQLPLUS.
We used the ALTER SESSION command to set OPTIMIZER_MODE to
ALL_ROWS and set OPTIMIZER_INDEX_ CACHING and
OPTIMIZER_INDEX_COST_ADJ to their default values.
We used code to create the Stored
Outline with the optimal access path. Since the statement must run successfully to create the Stored
Outline, we had to wrap a lot of additional code around the SQL
to get it to execute in SQLPLUS. Thatís because the SQL used
input bind variables, which you have to supply to get it to run.
In addition, SQLPLUS will choke if you select values from
columns in PL/SQL and donít put them in predefined output
variables. That is why all of those output variables are
identified. Because the tableís schema owner was not hard coded
in the SQL coming from the vendor, we logged on as the owner of
the tables to create the Stored Outline. You can use OEMís
Outline Management tool to determine whether or not the Stored
Outline is being used. The following screenshot of the Outline
Manager shows two Stored Outlines, their owners, and that they
were both used by at least one SQL statement.
Figure 1: Outline Manager.
Stored Outlines allowed us to change
several key SQL statements that were jeopardizing the
applicationís usefulness to our business users. The vendor is
currently changing their batch programs to embed the ALTER
SESSION commands required to influence the optimizer to create
more optimal access paths. Why didnít we catch this in test? It
looks like we may have hit a previously unknown Oracle bug on
which Oracle support is currently working.
You are also able to use OEMís
Outline Management tool to create Stored Outlines. Itís really
quite simple to use OEM to create the outline: You select CREATE
from the Outline Management tool menu on the left and paste the
SQL statement into the box that appears. Thatís it! When you
become proficient at SQL tuning, you can then use OEMís
Outline Editor tool to change the outlineís stored access path.
You can access OEMís Outline Editor
tool by clicking on the EDIT OUTLINE button in the Outline
Management tool. It is important to note that you use the
Outline Editor tool to change the statementís access path, but
not the statement itself. If you look at this image of
the Outline Editor tool, youíll see that I have activated the
Join Method Properties Panel, which allows me to change the
statementís join method. You can also use the editor to
determine which table is accessed, first in the join; then you
can toggle back and forth between index-access and full-table
Figure 2: Outline Editor.
If you are experienced in tuning SQL and
know the access path you want, you can dump the SQL into the
Outline Management tool, create the Stored Outline, and then
activate the Outline Editor tool to manipulate the access path
to achieve optimal performance. Is this a great tool, or what?
Oracle also provides PL/SQL programs to perform the same
activities, but Iím now hooked on Oracleís SGT (Sissy GUI
So, why did we have to use SQLPLUS to
create our Stored Outlines and not OEMís Outline Editor
tool? Remember that we were using ALTER SESSION commands in
SQLPLUS to change the optimization mode to CHOOSE and reset some
index optimization settings to their default values; otherwise,
we would have used the Sissy GUI Tool to create and change the
statementsí access paths.
Freezing and changing a statementís access
path should only be done when you have exhausted all other
options. But the benefits that this feature provides should not
be understated. It helped us in our third-party SQL performance
emergency; now, we have another tool in our toolbox to use. The
more tools we have at our disposal, the more quickly we can
solve problems for our customers.