Question: I just bought a vendor
application and I need to change the execution plans for the SQL. The
vendor will not help, and I need to know how to modify SQL explain plans without
touching the vendor source SQL statements.
Answer: This is a common problem, and it's
addressed in the eBook
Oracle Vendor Systems. I also have some good notes on
tuning Oracle vendor
There are three ways to change SQL execution plans for
packages vendor applications where you cannot modify the source code:
Swap stored outlines: You
can swap stored outlines and change the execution plan for a SQL
For Oracle 10g and beyond, you can tune SQL remotely by
The dbms_sqldiag_internal package:
In general, there are several methods to swap-out a bad
execution plan with a tuned execution plan, and the approach depends on your
release level of Oracle:
Swapping stored Outlines
MOSC note 92202 .1 describes
a procedure to tune SQL that you cannot touch by performing these steps:
Identify the sub-optimal SQL and create a stored
Tune an equivalent query with a faster execution plan
and create a stored outline
Swap the bad stored outline for the tuned stored
Oracle provides this example for swapping the outlines:
OL_NAME IN ('HINTSQL','ORIGINALSQL');
The Oracle 10g automatic
tuning advisor allowed us to implement tuning suggestions in the form of SQL
profiles that will improve performance, and SQL profiles can be used the
same way as stored outlines (optimizer plan stability).
A SQL Profile is stored persistently in the data
dictionary, so it does not require any application code changes.
A SQL profile helps generate a better execution plan than
the normal optimization because it can be validated against a real-world workload in
the SQL Tuning Set (STS). Additional tasks like checking for advanced predicate
selectivity, correlation between columns, join skews, and complex predicates
such as functions, help in profiling the SQL statement. Once a SQL statement is
profiled and stored, differing execution plans can be invoked at will.
For a full discussion and examples, see my notes on
swapping SQL Profiles.
SQL Performance Analyzer (SPA)
In Oracle 11g, we see the
SQL Performance Analyzer, a holistic tool that allows the DBA to test Silver
Bullet settings (system-wide parms, changes to CBO stats) against a real-world
workload. In 11g,
Now with Oracle11g, the DBA
can tell Oracle to automatically apply SQL profiles for statements whenever the
suggested profile give 3-times better performance that the existing statement.
These performance comparisons are done by a new 11g administrative task that is
executed during a user-specified maintenance window.
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts.