SQL Plan Management (SPM) in Oracle

Many times we experience performance regressions because of many things i.e a execution plan has been changed.For this type of problem there is an elegant solution  called SQL Plan Management (SPM). 

Execution plan changes occur due to various system changes. For example, you might have (manually or automatically) updated statistics for some objects, or changed a few optimizer-related parameters. A more dramatic change is a database upgrade (say from 10gR2 to 11g). All of these changes have the potential to cause new execution plans to be generated for many of your SQL statements. Most new plans are obviously improvements because they are tailored to the new system environment, but some might be worse leading to performance regressions. 

DBAs have several options for addressing these regressions. However, what most DBAs want is simple: plans should only change when they will result in performance gains. In other words, the optimizer should not pick bad plans, period. 

SQL plan management provides a mechanism for maintaining consistent SQL performance regardless of changes in optimizer version, optimizer statistics, schema changes, system settings and SQL profile creation.

How SPM Works:

When a SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer compares the plan it just produced with the plans in the SQL plan baseline. If a matching plan is found that is flagged as accepted the plan is used. If the SQL plan baseline doesn't contain an accepted plan matching the one it just created, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost. If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, so they are considered non-reproducible, the optimizer will use the plan with the lowest cost.

Oracle call this a "conservative plan selection strategy", as the optimizer preferentially uses a tried an tested execution plan, even if a new plan looks like it might perform better. Only when the newer plan is proved to perform well will it be accepted for use.

SQL PLAN MANAGER (SPM) has three major components

SQL Plan Baseline Capture - Create SQL Plan Baseline for trusted (accepted) SQL plans.

SQL Plan Baseline Selection - Conform that only accepted SQl plans are used when an SQL statement is executed.

SQL Plan Baseline Evolution - Evaluated all SQL Plans (old & new) for each SQL statement. The new/old plan will only be excepted if it gives better or equal performance compared with existing trusted plan otherwise rejected. (If no trusted plan exist then current plan will be accepted)

To enable this cool feature we just do the followings:

ALTER SYSTEM set optimizer_capture_sql_plan_baselines= TRUE scope=both; [Default value is false]

ALTER SYSTEM set optimizer_user_sql_plan_baselines= TRUE scope=both; [Default value is true, so just check the parameter value]