Oracle SQL Plan Management in practice: capture, load, fix and evolve SQL plan baselines

SQL Plan Management (SPM) lets the optimizer use only plans you have accepted for a statement. New plans are recorded but not used until verified. It is the most reliable way to keep critical SQL stable across stats gathering, patching and upgrades.

#Concepts

SHOW PARAMETER optimizer_capture_sql_plan_baselines   -- usually FALSE: no automatic capture
SHOW PARAMETER optimizer_use_sql_plan_baselines        -- TRUE: baselines are honoured

Leave automatic capture off on most systems; load baselines deliberately for important SQL.

#Load a good plan from the cursor cache

Find the statement and its good plan:

SELECT sql_id, plan_hash_value, executions, ROUND(elapsed_time / NULLIF(executions,0) / 1000) AS avg_ms
FROM v$sql WHERE sql_text LIKE 'SELECT /* orders_by_customer */%';

DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '7h35uxf5uhmm1', plan_hash_value => 2833921472);
  DBMS_OUTPUT.PUT_LINE(n || ' plan(s) loaded');
END;
/

#Load a good plan from AWR (after it left the cache)

When the good plan ran last week but is no longer cached, load it from AWR (requires Diagnostics Pack licensing):

DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_AWR(begin_snap => 41210, end_snap => 41260,
         basic_filter => q'[sql_id = '7h35uxf5uhmm1' AND plan_hash_value = 2833921472]');
  DBMS_OUTPUT.PUT_LINE(n);
END;
/

Older versions use a SQL tuning set as an intermediate step (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY, then LOAD_PLANS_FROM_SQLSET).

#See baselines

SELECT sql_handle, plan_name, enabled, accepted, fixed, origin, last_executed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE 'SELECT /* orders_by_customer */%';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle => 'SQL_8f1c2a...', format => 'TYPICAL'));

Verify the statement uses it:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7h35uxf5uhmm1', NULL, 'BASIC +NOTE'));
-- Note: SQL plan baseline "SQL_PLAN_8y3..." used for this statement

#Emergency: pin the good plan now

A regression is hurting production and you know the good plan hash. Load it (above), then fix it so it wins over others:

DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle => 'SQL_8f1c2a...', plan_name => 'SQL_PLAN_8y3...',
         attribute_name => 'FIXED', attribute_value => 'YES');
END;
/

New executions pick it up at the next hard parse. To force that for the bad cursor without flushing the whole shared pool:

SELECT address || ',' || hash_value FROM v$sqlarea WHERE sql_id = '7h35uxf5uhmm1';
EXEC DBMS_SHARED_POOL.PURGE('00000000A1B2C3D4,1234567890', 'C');

Warning

Never ALTER SYSTEM FLUSH SHARED_POOL on a busy production database to fix one statement. It forces every session to hard parse and can cause a library cache storm.

#Plans for SQL with literals or from vendor apps

Baselines match on the statement's signature (normalised text). If the application sends literal values that change, each variant has a different signature. Options: cursor_sharing=FORCE (risky globally), fixing the app to use binds, or SQL patches / SQL profiles instead. You can also attach a plan from a hinted version of the statement to the original SQL's baseline (load from the hinted statement's cursor, specifying the original sql_handle).

#Evolve: let better plans in

When data or indexes change, a new plan might be better. SPM records it as unaccepted. The automatic SPM evolve advisor task runs in the maintenance window (check its status), or run it manually:

SELECT task_name, enabled FROM dba_autotask_schedule_control WHERE task_name LIKE '%SPM%';  -- 19c+ names vary

VARIABLE rep CLOB
DECLARE
  t VARCHAR2(64);
  e VARCHAR2(64);
BEGIN
  t := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_8f1c2a...');
  e := DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => t);
  :rep := DBMS_SPM.REPORT_EVOLVE_TASK(task_name => t, execution_name => e);
  DBMS_OUTPUT.PUT_LINE(t);
END;
/
PRINT rep

The report shows each tested plan, its performance versus the accepted one, and whether it was accepted. Implement recommendations with DBMS_SPM.IMPLEMENT_EVOLVE_TASK if the task did not auto-accept.

#Upgrades and migrations

Before an upgrade (for example 19c to 23ai):

1. Capture critical SQL plans into a SQL tuning set on the old version.
2. Pack baselines into a staging table and move it (DBMS_SPM.CREATE_STGTAB_BASELINE, PACK_STGTAB_BASELINE, Data Pump, UNPACK_STGTAB_BASELINE).
3. After the upgrade, the optimizer keeps using old plans until evolve proves new ones better.

#Housekeeping

SELECT parameter_name, parameter_value FROM dba_sql_management_config;
EXEC DBMS_SPM.CONFIGURE('PLAN_RETENTION_WEEKS', 26);
EXEC DBMS_SPM.CONFIGURE('SPACE_BUDGET_PERCENT', 10);

Drop baselines that no longer apply:

DECLARE n PLS_INTEGER;
BEGIN n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => 'SQL_8f1c2a...'); END;
/

#Prevent it next time

• Keep a list of critical SQL (top elapsed time, business-critical screens) and load baselines for them.
• Review the evolve report weekly; investigate rejected plans that look better on paper.
• Pack and carry baselines through every upgrade and migration.