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.
The use of SQL plan baselines is controlled by the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter, which is set to TRUE by default.
Automatic Plan Capture
The value of the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter, whose default value is FALSE, determines if the system should automatically capture SQL plan baselines. When set to TRUE, the system records a plan history for SQL statements. The first plan for a specific statement is automatically flagged as accepted. Alternative plans generated after this point are not used until it is verified they do not cause performance degradations. Plans with acceptable performance are added to the SQL plan baseline during the evolution phase.
SQL> SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_capture_sql_plan_baselines boolean FALSE
SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;
Manual Plan Loading
Manual plan loading can be used in conjunction with, or as an alternative to automatic plan capture. The load operations are performed using the DBMS_SPM package, which allows SQL plan baselines to be loaded from SQL tuning sets or from specific SQL statements in the cursor cache. Manually loaded statements are flagged as accepted by default. If a SQL plan baseline is present for a SQL statement, the plan is added to the baseline, otherwise a new baseline is created.
The following code uses the LOAD_PLANS_FROM_SQLSET function to load all statements in an existing SQL tuning set into SQL baselines. A filter can be applied to limit the SQL statements loaded if necessary.
l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
sqlset_name => ‘my_sqlset’);
The LOAD_PLANS_FROM_CURSOR_CACHE functions allow SQL statements to be loaded from the cursor cache. There are four overloads, allowing statements to be identified by a number of criteria, including: SQL_ID, SQL_TEXT, PARSING_SCHEMA_NAME, MODULE and ACTION. The following example identifies the SQL statement using the SQL_ID.
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => ‘1fkh93md0802n’);
The return value of the LOAD_PLANS_FROM_SQLSET and LOAD_PLANS_FROM_CURSOR_CACHE functions indicates the number of plan loaded by the function call