SQL Plan Management Explained Via Kitchen Analogy

📌 Oracle 19c · EBS 12.2 · Performance Tuning

🍽️ SQL Plan Management:
Explained via Restaurant Kitchen Analogy

Oracle Apps DBA Lead  |  appsdbastuff.blogspot.com

🧠 SQL Plan Management (SPM) is Oracle's mechanism to prevent plan regressions — it captures, verifies, and enforces known-good execution plans so the optimizer never silently switches to a worse one. If you've ever scratched your head trying to explain baselines and profiles to a junior DBA, try this: it's exactly how a professional kitchen manages its recipes. This post walks through every SPM concept with a parallel restaurant analogy — side by side, step by step.

🗺️ The Complete Analogy Map

Before we dive deep, here's the full mapping at a glance:

Step	🔵 Oracle / SPM Concept	🍽️ Restaurant Analogy	Why It Maps
01	Cursor Cache — Optimizer picks plan	Chef decides how to cook the dish	Both choose the "best method" from available options at parse/order time
02	Capturing Plans — Auto / LOAD_PLANS	Chef writes down the recipe	Good method recorded so it can be reproduced consistently
03	SQL Plan Baseline — ACCEPTED store	Official approved menu recipe book	Only stamped, verified plans/recipes are used — no ad-hoc improvisation
◆	Decision gate — Plan regressed?	Expeditor quality check — dish pass/fail	Gate controls whether the plan/dish proceeds or gets sent back
04	SQL Profile — cardinality fix	Specialist seasoning — corrects without rewriting recipe	Auxiliary correction applied on top of the existing plan/recipe
05	AWR / ASH / SQL Monitor	Food critic + kitchen CCTV	Continuous measurement that feeds back into the improvement cycle

Now let's go through each step in detail.

🗃️ Step 01 — Cursor Cache & The Chef's Decision

STEP 01

🗃️

Cursor Cache / Library Cache

Hard parse → optimizer evaluates all access paths → cheapest plan loaded

🔵 Oracle Concept

V$SQL holds every parsed query — SQL_ID, PLAN_HASH_VALUE, elapsed time, executions

Optimizer evaluates FTS, index range scan, hash join, nested loop → picks lowest-cost plan

Bind variable peeking on first parse; adaptive cursor sharing for skewed histograms

SPM intercepts: if baseline exists, optimizer MUST use an ACCEPTED plan from it

🍽️ Restaurant Analogy

Guest places an order = Application submits a SQL query

Chef's mental process "how do I cook this?" = Optimizer generating the execution plan

Multiple cooking methods (grill / bake / fry) = multiple access paths (index / FTS / hash join)

Chef's recipe binder checked first = SPM baseline consulted before new plan is used

🔍 Key V$SQL Query — Find Heavy SQLs

-- Top 10 SQLs by elapsed time with plan hash — starting point for SPM
SELECT sql_id,
       plan_hash_value,
       ROUND(elapsed_time/NULLIF(executions,0)/1e6,2)  elapsed_sec_per_exec,
       executions,
       buffer_gets,
       SUBSTR(sql_text,1,80)                            sql_preview
  FROM  v$sql
 WHERE  executions > 10
 ORDER BY elapsed_time DESC
 FETCH FIRST 10 ROWS ONLY;

📡 Step 02 — Capturing Plans & Writing the Recipe

STEP 02

📡

Capturing Plans into SPM

Auto capture · bulk load from cursor cache or AWR · staging table for transport

🔵 Oracle Concept

Auto capture: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE — every repeatable SQL gets its plan saved automatically

Manual load: DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE — target specific SQL_IDs from V$SQL

AWR load: DBMS_SPM.LOAD_PLANS_FROM_AWR — pull historical plans from DBA_HIST_SQLSTAT

New plan for same SQL signature → ENABLED but NOT ACCEPTED until evolution approves it

🍽️ Restaurant Analogy

Auto capture = chef reflexively jotting down the method every time a popular dish turns out perfectly

Manual load = head chef deliberately documents a recipe after a guest praises it

AWR load = reviewing last month's kitchen log to find which method produced the best-rated dish

New recipe variation noted separately — not yet on the approved menu until trialled and approved

🔧 Load Plans — Auto Capture + Manual Bulk Load

-- Enable automatic capture (set at instance or session level)
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE SCOPE=BOTH;

-- Manual bulk load from cursor cache for a specific SQL_ID
DECLARE
  l_count  PLS_INTEGER;
BEGIN
  l_count := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
               sql_id        => '&your_sql_id',
               plan_hash_value => NULL   -- NULL = load ALL plans for this SQL
             );
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_count);
END;
/

-- Load from AWR (useful for plans no longer in cursor cache)
DECLARE
  l_count  PLS_INTEGER;
BEGIN
  l_count := DBMS_SPM.LOAD_PLANS_FROM_AWR(
               begin_snap => 12000,
               end_snap   => 12050,
               basic_filter => 'sql_id = ''&your_sql_id'''
             );
  DBMS_OUTPUT.PUT_LINE('Plans loaded from AWR: ' || l_count);
END;
/

📋 Step 03 — SQL Plan Baseline & The Official Recipe Book

STEP 03

📋

SQL Plan Baseline — The Accepted Plan Store

DBA_SQL_PLAN_BASELINES · SYSAUX · ENABLED / ACCEPTED / FIXED / REPRODUCED flags

🔵 Oracle Concept

ACCEPTED=YES: optimizer ONLY uses these plans — all others are tried but fall back here

FIXED=YES: overrides cost model changes, stats recompute, patch upgrades — hard lock

Evolution: DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE promotes a new plan only if ≥1.5× faster

Baseline survives DB bounces, ADOP patch cycles, stats recomputes — plan stability guaranteed

🍽️ Restaurant Analogy

ACCEPTED recipe = dish is on the official printed menu; every cook follows the exact steps

FIXED=YES = "signature dish" locked by the head chef — no substitutions, no improvisation ever

Evolution = new preparation method trialled on back-kitchen; only promoted if significantly faster

Recipe survives staff changes, seasonal ingredient swaps — consistent guest experience every visit

🔍 Query Baselines — Inspect Your SPM Store

-- View all baselines for a specific SQL
SELECT sql_handle,
       plan_name,
       enabled,
       accepted,
       fixed,
       reproduced,
       origin,
       created,
       last_executed,
       description
  FROM  dba_sql_plan_baselines
 WHERE  sql_text LIKE '%your_table_or_keyword%'
 ORDER BY created DESC;

-- Pin the known-good plan (set FIXED=YES) — the "signature dish" lock
DECLARE
  l_cnt  PLS_INTEGER;
BEGIN
  l_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
             sql_handle    => 'SQL_&handle_value',
             plan_name     => 'SQL_PLAN_&plan_value',
             attribute_name  => 'fixed',
             attribute_value => 'YES'
           );
  DBMS_OUTPUT.PUT_LINE('Plans altered: ' || l_cnt);
END;
/

-- Evolve (promote a new plan if it's provably 1.5x faster)
DECLARE
  l_rep  CLOB;
BEGIN
  l_rep := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
             sql_handle  => 'SQL_&handle_value',
             verify      => 'YES',
             commit      => 'YES'
           );
  DBMS_OUTPUT.PUT_LINE(l_rep);
END;
/

◆ Decision Gate — Does the Plan Perform? Did the Dish Pass QC?

◆ Quality Gate — Accept or Reject

✅ YES — Plan Accepted / Dish Passes

• Oracle: Accepted baseline plan used for all executions
• FIXED=YES pins it through upgrades and stats recomputes
• Monitor V$SQL_PLAN_MONITOR + DBA_HIST_SQLSTAT trends
• Auto-evolution promotes proven-better plans (1.5× rule)
• Kitchen: Expeditor approves — correct taste, temp, plating
• Dish dispatched to table within target SLA

⚠️ NO — Plan Regressed / Dish Rejected

• Oracle: Disable bad plan — DBMS_SPM.ALTER_SQL_PLAN_BASELINE
• FIXED=YES on last known-good plan — immediate protection
• Collect 10053 trace + AWR Diff Report for root cause
• Escalate: SQL Profile or SQLT hint injection
• Kitchen: Chef remakes — root cause logged (overcook / wrong mod)
• Waiter updates table; SLA breach flagged in shift log

-- Disable a bad/regressed baseline plan immediately
DECLARE
  l_cnt  PLS_INTEGER;
BEGIN
  l_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
             sql_handle      => 'SQL_&handle_value',
             plan_name       => 'SQL_PLAN_&bad_plan',
             attribute_name  => 'enabled',
             attribute_value => 'NO'   -- disable the regressed plan
           );
  DBMS_OUTPUT.PUT_LINE('Disabled plans: ' || l_cnt);
END;
/

🎯 Step 04 — SQL Profile & The Specialist Seasoning

STEP 04

🎯

SQL Profile — Auxiliary Correction Object

Corrects cardinality errors · no SQL text change · stored by signature in SYSAUX

🔵 Oracle Concept

STA-generated: DBMS_SQLTUNE.EXECUTE_TUNING_TASK → ACCEPT_SQL_PROFILE on recommendation

Fixes stale statistics misestimates — optimizer receives corrected cardinality hints

Stored in SYSAUX by SQL signature — applies across environments, survives reparsing

Profile + Baseline together: profile corrects cardinality → baseline enforces plan shape

🍽️ Restaurant Analogy

Stale stats = chef misjudged how salty the stock was — dish is off, but the recipe itself is fine

SQL Profile = specialist's seasoning note clipped to the recipe card — corrects without rewrite

Seasoning note transfers between kitchens (UAT → PROD) like a transportable profile

Both seasoning note (profile) + official recipe (baseline) applied together = consistent dish

🔧 SQL Tuning Advisor — Create & Accept a SQL Profile

-- Step 1: Create a tuning task for the problematic SQL
DECLARE
  l_task  VARCHAR2(100);
BEGIN
  l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
              sql_id        => '&your_sql_id',
              scope         => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
              time_limit    => 300,           -- 5 minutes
              task_name     => 'TUNE_&your_sql_id',
              description   => 'Profile for regressed SQL'
            );
  DBMS_OUTPUT.PUT_LINE('Task: ' || l_task);
END;
/

-- Step 2: Execute the tuning task
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TUNE_&your_sql_id');

-- Step 3: Review the recommendation
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_&your_sql_id') FROM dual;

-- Step 4: Accept the SQL Profile
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
       task_name    => 'TUNE_&your_sql_id',
       replace      => TRUE,
       force_match  => TRUE    -- apply across similar SQL with different literals
     );

-- Verify profile is active
SELECT name, sql_text, status, force_matching
  FROM  dba_sql_profiles
 WHERE  name LIKE 'SYS_SQLPROF%'
 ORDER BY created DESC;

📊 Step 05 — Logging, AWR & The Food Critic Review

STEP 05

📊

Performance Logging & Monitoring

AWR · ASH · SQL Monitor · EBS FND logs · DBMS_SCHEDULER alert on plan hash drift

🔵 Oracle Tools → 🍽️ Restaurant Analogy

AWR (DBA_HIST_SQLSTAT) = Nightly food critic score — compares elapsed_time_delta today vs. last week's baseline

ASH (V$ACTIVE_SESSION_HISTORY) = Table-side observer watching where time is spent — grill, plating, or waiting for ingredients?

SQL Monitor (V$SQL_PLAN_MONITOR) = Live kitchen CCTV — see exactly which station is bottlenecked right now, with row counts per step

EBS FND_LOG_MESSAGES / AD_CONCURRENT_PROGRAMS = Restaurant POS order log — request_id, elapsed, phase cross-referenced with kitchen KOT times

DBMS_SCHEDULER alert on PLAN_HASH_VALUE change = Automated alarm if today's dish takes 3× longer → kitchen manager alerted instantly

🔧 AWR Plan Hash Drift Detection Query

-- Detect plan hash changes for a SQL over the last 7 days
SELECT s.snap_id,
       TO_CHAR(sn.end_interval_time, 'DD-MON HH24:MI')   snap_time,
       q.plan_hash_value,
       ROUND(q.elapsed_time_delta / NULLIF(q.executions_delta,0) / 1e6, 2) elapsed_sec,
       q.executions_delta                                    execs,
       q.buffer_gets_delta
  FROM  dba_hist_sqlstat   q
  JOIN  dba_hist_snapshot  sn  ON sn.snap_id = q.snap_id
                                AND sn.dbid    = q.dbid
                                AND sn.instance_number = q.instance_number
 WHERE  q.sql_id            = '&your_sql_id'
   AND  sn.end_interval_time > SYSDATE - 7
 ORDER BY s.snap_id;

-- Real-time SQL Monitor for active long-running query
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
         sql_id      => '&your_sql_id',
         report_level=> 'ALL',
         type        => 'TEXT'
       ) FROM dual;

🗂️ Key Dictionary Views — Quick Reference

DBA_SQL_PLAN_BASELINES

SQL_HANDLE · PLAN_NAME · ENABLED · ACCEPTED · FIXED · ORIGIN · CREATED · LAST_EXECUTED

DBA_SQL_PROFILES

NAME · STATUS · FORCE_MATCHING · CREATED · LAST_MODIFIED · SQL_TEXT

DBA_HIST_SQLSTAT

SQL_ID · PLAN_HASH_VALUE · SNAP_ID · ELAPSED_TIME_DELTA · BUFFER_GETS_DELTA · EXECUTIONS_DELTA

V$SQL_PLAN_MONITOR

SQL_ID · SQL_EXEC_ID · PLAN_LINE_ID · OUTPUT_ROWS · ELAPSED_TIME (real-time per step)

DBA_ADVISOR_FINDINGS

TASK_NAME · TYPE · MESSAGE · BENEFIT_TYPE — SQL Tuning Advisor recommendations

V$ACTIVE_SESSION_HISTORY

SQL_ID · EVENT · WAIT_CLASS · SESSION_STATE · SAMPLE_TIME — real-time ASH samples

📌 SPM Command Cheat Sheet

DBMS_SPM Procedure	🔵 Oracle Purpose	🍽️ Kitchen Equivalent
LOAD_PLANS_FROM_CURSOR_CACHE	Capture live plan from shared pool	Write down today's successful recipe
LOAD_PLANS_FROM_AWR	Recover historical plan from AWR	Dig out last month's kitchen log
ALTER_SQL_PLAN_BASELINE (FIXED=YES)	Hard-lock a plan — overrides optimizer	Head chef stamps recipe — no deviations
ALTER_SQL_PLAN_BASELINE (ENABLED=NO)	Disable a bad/regressed plan	Pull bad recipe off the menu immediately
EVOLVE_SQL_PLAN_BASELINE	Promote new plan if ≥1.5× faster	Upgrade recipe only after blind taste test proves it better
PACK_STGTAB_BASELINE	Export baselines to staging table	Print recipe book for other branch kitchens
UNPACK_STGTAB_BASELINE	Import baselines from staging table	New kitchen receives and adopts the recipe book
DROP_SQL_PLAN_BASELINE	Permanently delete a baseline	Remove discontinued dish from recipe archive

💡 Pro Tip — EBS 12.2 on Oracle 19c RAC

In EBS 12.2 environments, always capture baselines after a successful ADOP patch cycle on the run edition, not the patch edition. Plans captured on patch FS may differ due to the different APPS schema state. Use FIXED=YES sparingly — only for confirmed production-critical SQLs like concurrent program queries, AR/AP period-end batch SQLs, and MSC/VCP data collection queries where plan instability has caused SLA breaches.

✅ Summary — The Complete Analogy in One Line

SPM is Oracle's kitchen management system: the cursor cache is where the chef decides how to cook, capturing plans is writing the recipe down, the baseline is the approved recipe book (with FIXED=YES as the signature dish lock), the SQL Profile is the specialist's seasoning note that corrects the recipe without rewriting it, and AWR/ASH/SQL Monitor is your food critic, CCTV, and shift log — all feeding back into the next improvement cycle.

The beauty of SPM is that it enforces plan stability the same way a great kitchen enforces quality: no improvisation on the night of service. New ideas are always welcome, but they go through rigorous testing before they reach the guest's table.