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
Tuning Third-Party
Oracle Vendor Systems. I also have some good notes on
tuning Oracle vendor
applications.
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
In Oracle8i and Oracle9i, MetaLink 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
outline
-
Tune an equivalent query with a faster execution plan
and create a stored outline
-
Swap the bad stored outline for the tuned stored
outline
Oracle provides this example for swapping the outlines:
UPDATE
OUTLN.OL$HINTS
SET
OL_NAME=DECODE
(OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL')
WHERE
OL_NAME IN ('HINTSQL','ORIGINALSQL');
Commit;
SQL Profiles
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). The SQL Profile is
a collection of the historical information of prior runs of the SQL statement,
comparison details of the actual and estimated cardinality and predicate
selectivity, etc.
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 is tested 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.
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
scripts.
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts. |
