Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Information in this document applies to any platform.
SQL Plan management (SPM) is a preventative mechanism that records and evaluates the execution plans of SQL
statements over time. SPM builds SQL plan baselines composed of a set of existing plans that are known to be
efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless
of changes occurring in the system, providing "plan stability".
Common usage scenarios where SQL plan management can improve or preserve SQL performance include:
A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage
of SQL statements, with most of the plan changes resulting in either no performance change or improvement.
However, certain plan changes may cause performance regressions. The use of SQL plan baselines significantly
minimizes potential performance regressions resulting from a database upgrade.
Ongoing system and data changes can impact plans for some SQL statements, potentially causing performance
regressions. The use of SQL plan baselines will help to minimize performance regressions and stabilize SQL
Deployment of new application modules means introducing new SQL statements into the system. Application
software may use appropriate SQL execution plans developed under a standard test configuration for the new
SQL statements. If your system configuration is significantly different from the test configuration, the SQL plan
baselines can be evolved over time to produce better performance.
If you have a good plan in the cursor cache, then you can load these into SPM so that you can use this baseline to
preserve the performance. The following example command illustrates this:
set serveroutput on
var n number
fixed =>'NO', enabled=>'YES');
EXEC dbms_output.put_line('Number of plans loaded: ' || :n);
After enabling the SPM, for those SQL ID's that have SQL plan management baselines, the database will not collect
new access plans (even after SPM is disabled - assuming the baseline is active)
The purpose of this Document is to show an example of SQL PLAN MANAGEMENT.
NOTE: The steps to accept and un-accept the baseline have changed in 11gR2. For information on how to run
these steps in 11gR2, please see:
Document 1309799.1 How to Accept & Unaccept the plans in sql baselines in 126.96.36.199
The following is a script that you can run to demonstrate how SPM operates and then apply the base principles to your
actual code. The script contains remarks to explain what is happening as it runs. The script was originally created and
tested on 188.8.131.52.0 in the SH schema. Output may be slightly different on other versions.