SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 223

SQL Plan Management
An execution plan for a statement can change for a variety of reasons, such as optimizer version, optimizer statistics,
optimizer parameter's schema changes, and SQL profile creation. SQL Plan Management (SPM) is a feature
introduced in Oracle Database 11g that enables the system to automatically control the SQL plan by maintaining
SQL plan baselines. With this feature enabled, a newly generated plan can be used only if the Oracle optimizer can
determine that using the new plan will not impact the performance of the statement, in which case the optimizer will
use the SQL plan stored from an earlier execution of the same statement. However, if the optimizer determines that
the SQL plan is more efficient compared to the previous plan, it will use the new plan and will save this new plan as
the new plan baseline for future use. Incorporating such a feature into the system provides performance stability of
the system by avoiding plan regressions.
The SPM feature is enabled when the parameter OPTIMIZER_USE_SQL_PLAN_BASELINES is set to TRUE (default).
OPTIMIZER_USE_SQL_PLAN_BASELINES controls the use of SQL plan baselines. When enabled, the optimizer looks
for plans in SQL plan baselines for the SQL statement being complied. If any are found, then the optimizer will cost
each plan in the SQL plan baseline and pick the one with the lowest cost.

■ Note plan baseline can also manually be created using DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE .

For SQL statements that are executed frequently, the optimizer can maintain a history of its execution plans.
To determine which of the statements are repeatable, the optimizer maintains a statement log. An SQL statement is
considered repeatable when it is parsed or executed again after it has been logged. For these repeatable statements,
the execution plans generated by the optimizer are maintained as a plan history containing information such as SQL
text, outline, bind variables, and so forth, which is required by the optimizer to reproduce an execution plan if the
statement is parsed or executed in the future.
As obviously noticeable, Figure 7-4 is history or a collection of execution plans for a given SQL statement
generated over time. The first time a SQL statement is recognized as repeatable; the best-cost plan generated by the
optimizer will not be in the history and will be used. This first plan becomes the baseline. Subsequently, when a
new plan is generated using the best-cost method, the plan is added to the plan history as an unaccepted plan. The
optimizer then tries to find a matching plan in the SQL plan baseline and picks the plan with better performance
(lowest cost), which is then integrated into the SQL plan baselines. When it is verified that an unaccepted plan
does not cause any performance regression, the plan is changed to an accepted plan and integrated into the SQL
plan baseline. Not all plans in the history are used; only plans that have been verified to not cause any performance
regression would be used.