Load a good plan from the cursor cache as a SQL plan baseline

Command

-- 1) Compare the SQL's plans and their performance:
SELECT sql_id, child_number, plan_hash_value, executions,
       ROUND(elapsed_time/1e3/NULLIF(executions,0),1) AS ms_per_exec,
       sql_plan_baseline
FROM   v$sql
WHERE  sql_id = '<sql_id>';

-- 2) Load the chosen plan as a baseline:
SET SERVEROUTPUT ON
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
         sql_id          => '<sql_id>',
         plan_hash_value => <plan_hash_value>,
         fixed           => 'NO',
         enabled         => 'YES');
  DBMS_OUTPUT.PUT_LINE(n || ' plan(s) loaded');
END;
/

-- 3) Verify the baseline:
SELECT sql_handle, plan_name, origin, enabled, accepted, fixed, reproduced,
       TO_CHAR(last_executed,'YYYY-MM-DD HH24:MI') AS last_exec
FROM   dba_sql_plan_baselines
WHERE  signature = (SELECT exact_matching_signature FROM v$sql
                    WHERE sql_id = '<sql_id>' AND ROWNUM = 1);
-- On subsequent executions v$sql.sql_plan_baseline should be populated.

What it does

Uses SQL Plan Management to make the chosen plan an ACCEPTED baseline; the optimizer then uses only accepted baseline plans and parks newly found plans until they are evolved.

When to use it

To return to a known good plan during a plan regression, and to protect critical SQL plans before an upgrade or statistics change.

Caution

No extra license on Enterprise Edition (on SE2 it is limited to one baseline plan per SQL). Requires ADMINISTER SQL MANAGEMENT OBJECT. A baseline applies to the exact same SQL text (signature); it does not help SQL that uses literals. If an index used by the plan is dropped, the plan cannot be reproduced (REPRODUCED=NO) and the optimizer falls back to cost-based choice. OPTIMIZER_USE_SQL_PLAN_BASELINES must be TRUE (default).