SQL Plan Management — guaranteed performance

SQL Plan Management is the long-awaited answer to execution plans that change without warning. It guarantees that a plan only ever changes for the better.

Overview

The performance of any database depends heavily on the execution plans it uses. In the past, plans that changed on their own were a daily challenge in database operations. In some cases plans were pinned through stored outlines, or statistics were frozen and never regathered.

SQL Plan Management now allows execution plans to be administered consistently for the first time. It ensures that execution plans can only change for the better: the optimizer manages existing plans and uses only those that are known to be good or have been verified by the database. A newly adopted plan therefore always performs comparably or better, never worse.

SPM consists of three components:

SQL plan baseline capture

Creates accepted (trusted) execution plans for all relevant SQL statements and stores them in the SQL management base in the SYSAUX tablespace.

SQL plan baseline selection

Guarantees that only accepted plans are used. All new execution plans are stored in a plan history. A plan can therefore be accepted (currently in use), not accepted and new (a newly found plan), or not accepted and rejected (a verified plan with worse performance).

SQL plan baseline evolution

Evaluates all newly found plans and marks them as accepted or rejected.

Enabling SPM

Several methods can be used to populate the SQL management base. The simplest is to capture the plans currently in use.

alter system set optimizer_capture_sql_plan_baselines=true scope=both;

.

Note: on databases with a large number of distinct SQL statements, or where bind variables are not used, the SQL management base can grow very large very quickly. In our tests this caused serious problems.

To use SPM it has to be enabled. This is independent of the capture option mentioned above.

alter system set optimizer_use_sql_plan_baselines= TRUE;

.