SQL Plan Management — DBA Important Views, Tables, and Approach

1. What is SQL Plan Management?

SQL Plan Management, commonly called SPM, is used to protect important SQL statements from sudden execution plan changes.

In simple production language:

SPM helps the optimizer use only known and verified good plans, instead of suddenly choosing a new bad plan after statistics refresh, patching, upgrade, parameter change, or data growth.

Your Oracle 19c SQL Tuning Workshop material says SQL plan baselines are used to prevent unverified execution plan changes, and a new plan should not be used until it is verified as better than the current accepted plan.

2. Most Important SPM Views for DBA

A. `DBA_SQL_PLAN_BASELINES`

This is the main SPM view.

Use it to check existing SQL plan baselines.


SELECT signature,
       sql_handle,
       plan_name,
       origin,
       enabled,
       accepted,
       fixed,
       autopurge,
       created,
       last_executed,
       sql_text
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%&TEXT_PATTERN%'
ORDER BY created DESC;

Important Columns

Column	Meaning for DBA
`SIGNATURE`	Internal SQL signature
`SQL_HANDLE`	Parent identifier for SQL baseline
`PLAN_NAME`	Name of specific plan
`ORIGIN`	How baseline was created
`ENABLED`	Whether optimizer can consider this plan
`ACCEPTED`	Whether plan is verified and usable
`FIXED`	Whether optimizer should prefer only fixed plans
`AUTOPURGE`	Whether plan can be purged automatically
`CREATED`	When baseline was created
`LAST_EXECUTED`	When plan was last used
`SQL_TEXT`	SQL text attached to baseline

Your Oracle training guide highlights SIGNATURE, SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, and AUTOPURGE as important SQL plan baseline attributes. It also explains that ENABLED and ACCEPTED must be YES for the optimizer to consider the plan.

B. `DBA_SQL_MANAGEMENT_CONFIG`

Use this to check SPM configuration.


SELECT parameter_name,
       parameter_value
FROM   dba_sql_management_config;

Important parameters:

Parameter	Purpose
`SPACE_BUDGET_PERCENT`	Space limit for SQL Management Base
`PLAN_RETENTION_WEEKS`	How long unused plans are retained

Example:


EXEC DBMS_SPM.CONFIGURE('SPACE_BUDGET_PERCENT',20);
EXEC DBMS_SPM.CONFIGURE('PLAN_RETENTION_WEEKS',105);

Your SQL Tuning Workshop material shows DBMS_SPM.CONFIGURE being used for SPACE_BUDGET_PERCENT and PLAN_RETENTION_WEEKS.

C. `V$SQL`

Use this to confirm whether a currently running SQL is using a baseline.


SELECT inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       sql_plan_baseline,
       sql_profile,
       sql_patch,
       executions,
       elapsed_time/1000000 elapsed_sec,
       buffer_gets,
       disk_reads,
       rows_processed
FROM   gv$sql
WHERE  sql_id = '&SQL_ID';

Important columns:

Column	Meaning
`SQL_PLAN_BASELINE`	Shows baseline used by current cursor
`SQL_PROFILE`	Shows SQL Profile used
`SQL_PATCH`	Shows SQL Patch used
`PLAN_HASH_VALUE`	Current execution plan
`CHILD_NUMBER`	Child cursor number
`ELAPSED_TIME`	Total elapsed time
`BUFFER_GETS`	Logical reads
`DISK_READS`	Physical reads

Your lab material shows verifying baseline usage from the execution plan note and by checking SQL_PLAN_BASELINE in V$SQL.

D. `V$SQL_PLAN`

Use this for current execution plan operations.


SELECT *
FROM   gv$sql_plan
WHERE  sql_id = '&SQL_ID'
ORDER BY inst_id, child_number, id;

Use when:

Scenario	Why
SQL is currently running	See current plan operations
Need to check full table scan	Look at access path
Need join method	Check nested loop/hash join/merge join
Need object names	Check tables/indexes used

E. `V$SQL_PLAN_STATISTICS_ALL`

Use this when you need estimated rows vs actual rows.


SELECT inst_id,
       sql_id,
       child_number,
       id,
       operation,
       options,
       object_owner,
       object_name,
       cardinality estimated_rows,
       last_output_rows actual_rows,
       last_cr_buffer_gets,
       last_disk_reads
FROM   gv$sql_plan_statistics_all
WHERE  sql_id = '&SQL_ID'
ORDER BY inst_id, child_number, id;

Use this to find:

Finding	Meaning
Estimated rows very low but actual rows high	Cardinality issue
High buffer gets at one step	Expensive plan operation
High disk reads at one step	I/O-heavy operation
Hash join with high rows	Possible TEMP spill
Nested loop with huge rows	Bad join method

F. `DBA_HIST_SQLSTAT`

Use this for historical SQL performance.


SELECT sql_id,
       plan_hash_value,
       SUM(executions_delta) executions,
       ROUND(SUM(elapsed_time_delta)/1000000/60,2) elapsed_mins,
       ROUND(SUM(cpu_time_delta)/1000000/60,2) cpu_mins,
       SUM(buffer_gets_delta) buffer_gets,
       SUM(disk_reads_delta) disk_reads,
       SUM(rows_processed_delta) rows_processed,
       ROUND(SUM(elapsed_time_delta)/1000000 /
             NULLIF(SUM(executions_delta),0),2) avg_elapsed_sec
FROM   dba_hist_sqlstat
WHERE  sql_id = '&SQL_ID'
GROUP BY sql_id, plan_hash_value
ORDER BY avg_elapsed_sec DESC;

This is one of the most important queries before using SPM.

It answers:

Question	Answer
Did plan change?	Multiple `PLAN_HASH_VALUE`
Which plan was good?	Lower average elapsed time
Which plan was bad?	Higher elapsed, buffer gets, disk reads
Was it plan issue or volume issue?	Compare executions and rows processed

G. `DBA_HIST_SQL_PLAN`

Use this to find historical plan operations.


SELECT id,
       operation,
       options,
       object_owner,
       object_name,
       cardinality,
       cost
FROM   dba_hist_sql_plan
WHERE  sql_id = '&SQL_ID'
AND    plan_hash_value = '&PLAN_HASH_VALUE'
ORDER BY id;

Use this when:

Scenario	Purpose
Current bad plan aged out	Get plan from AWR
Need old good plan	Compare historical plan
Need object list	Identify tables/indexes
Need plan_hash evidence	RCA and SPM decision

H. `DBA_HIST_SQLTEXT`

Use this to get historical SQL text.


SELECT sql_id,
       DBMS_LOB.SUBSTR(sql_text, 4000, 1) sql_text
FROM   dba_hist_sqltext
WHERE  sql_id = '&SQL_ID';

I. `DBA_HIST_ACTIVE_SESS_HISTORY`

Use this to prove whether the SQL was delayed by CPU, I/O, TEMP, blocking, RAC, or parallel waits.


SELECT sql_id,
       sql_plan_hash_value,
       wait_class,
       event,
       COUNT(*) ash_samples
FROM   dba_hist_active_sess_history
WHERE  sql_id = '&SQL_ID'
AND    sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
                       AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY sql_id,
         sql_plan_hash_value,
         wait_class,
         event
ORDER BY ash_samples DESC;

Use this before SPM because:

ASH Result	Meaning
CPU + plan changed	SPM may help
I/O + plan changed	SPM may help
TEMP + plan changed	SPM may help
Blocking	SPM will not fix
RAC cluster wait	SPM may not fix unless plan causes hot blocks
Parallel wait	Check DOP/PX first
Same plan but more rows	SPM may not help

J. `V$SQL_SHARED_CURSOR`

Use this to check why multiple child cursors exist.


SELECT *
FROM   gv$sql_shared_cursor
WHERE  sql_id = '&SQL_ID';

Important when:

Scenario	Why
Same SQL has many child cursors	Possible bind/optimizer mismatch
Different plans for same SQL	Need child cursor analysis
Bind-sensitive SQL	SPM may stabilize plan, but bind behavior still matters

K. `GV$SQLAREA`

Use this for SQL summary across RAC.


SELECT inst_id,
       sql_id,
       plan_hash_value,
       executions,
       elapsed_time/1000000 elapsed_sec,
       cpu_time/1000000 cpu_sec,
       buffer_gets,
       disk_reads,
       rows_processed,
       module,
       action
FROM   gv$sqlarea
WHERE  sql_id = '&SQL_ID';

3. Important DBMS Packages for SPM

Main package: `DBMS_SPM`

Procedure / Function	Purpose
`LOAD_PLANS_FROM_CURSOR_CACHE`	Load current cursor plan into SPM
`LOAD_PLANS_FROM_AWR`	Load historical AWR plan into SPM
`LOAD_PLANS_FROM_SQLSET`	Load plans from SQL Tuning Set
`ALTER_SQL_PLAN_BASELINE`	Enable, disable, accept, fix, unfix plans
`DROP_SQL_PLAN_BASELINE`	Drop unwanted baseline
`EVOLVE_SQL_PLAN_BASELINE`	Test/evolve new plan
`CREATE_EVOLVE_TASK`	Create SPM evolve task
`EXECUTE_EVOLVE_TASK`	Execute evolve task
`REPORT_EVOLVE_TASK`	View evolve report
`IMPLEMENT_EVOLVE_TASK`	Accept recommended evolved plans

Your Oracle material explains that non-accepted plans are verified before being accepted, and plan evolution compares a non-accepted plan against an existing accepted baseline to avoid regression. It also lists task-based SPM Evolve Advisor functions such as CREATE_EVOLVE_TASK, EXECUTE_EVOLVE_TASK, REPORT_EVOLVE_TASK, and IMPLEMENT_EVOLVE_TASK.

4. When to Use SQL Plan Management

Scenario 1: SQL Plan Regression After Stats Gather

Use SPM when:

Evidence	Meaning
Same SQL_ID has multiple plan hash values	Plan changed
Old plan was fast	Known good plan exists
New plan is slow	Regression confirmed
ASH shows CPU/I/O/TEMP increased	Bad plan caused more work

Approach:


1. Compare old good plan and current bad plan.
2. Confirm good plan was actually faster.
3. Load good plan from cursor cache or AWR.
4. Accept and enable the good baseline.
5. Re-execute and verify baseline usage.

Good for month-end because stats refresh can cause sudden optimizer plan changes.

Scenario 2: After Database Upgrade or Optimizer Version Change

Use SPM when upgrading from lower version to 19c or changing optimizer behavior.

Your Oracle guide says bulk loading SQL plans is useful during database upgrade because plans can be captured into a SQL Tuning Set before upgrade and loaded into SQL plan baselines after upgrade to reduce plan regressions from the new optimizer version.

Approach:


1. Capture critical SQL workload before upgrade.
2. Create SQL Tuning Set.
3. Load plans into SQL Plan Baselines.
4. After upgrade, monitor for new better plans.
5. Evolve only verified better plans.

Scenario 3: Critical Month-End SQL Suddenly Runs Long

Use SPM when:

Condition	SPM Suitable?
Critical financial close SQL changed plan	Yes
Known good plan exists in AWR	Yes
SQL cannot be changed because it is seeded EBS code	Yes
Business needs immediate safe workaround	Yes, after validation
Issue is blocking	No
Issue is TEMP due to huge data volume with same plan	Maybe no
Issue is PX server shortage	No, fix DOP/capacity

Scenario 4: Application SQL Cannot Be Modified

Use SPM when SQL comes from Oracle EBS seeded code or vendor application and you cannot add hints in code.

Your Oracle guide says SQL plan baselines can be created for SQL statements from applications where the SQL cannot be modified, or where a hinted plan is needed for good execution.

Approach:


1. Do not change seeded EBS SQL code.
2. Find known good plan.
3. Load it into SPM.
4. Validate with business-approved testing.
5. Monitor after next execution.

Scenario 5: Hinted Plan Works in Testing but Original SQL Cannot Be Changed

Use SPM when a hinted test SQL gives a good plan, but production SQL text cannot be modified.

Oracle’s SPM example says to capture the original SQL baseline, execute hinted SQL, find hinted SQL_ID and plan hash value, then associate the hinted execution plan with the original SQL handle.

High-level approach:


1. Capture original SQL baseline.
2. Run hinted SQL in controlled test/session.
3. Find hinted SQL_ID and plan hash value.
4. Load hinted plan into original SQL_HANDLE.
5. Disable/drop original bad baseline if required.
6. Verify original SQL uses the new baseline.

5. When NOT to Use SPM

SPM is powerful, but not every performance issue is a plan issue.

Scenario	Why SPM Is Not First Choice
Blocking / row lock contention	Plan baseline will not remove lock
TEMP full due to large business volume	May need SQL/stats/PGA/TEMP capacity review
Parallel servers exhausted	Need DOP governance, not SPM
Program submitted multiple times	Need business process control
Object statistics are stale	First validate stats
Data volume changed permanently	Old plan may not remain best
Wrong bind values / skew issue	Need bind/cardinality analysis
RAC interconnect issue	Need cluster wait analysis
Storage latency	Need I/O investigation
Bad application logic	Need functional/application fix

Production rule:

Use SPM only after proving that plan change is the reason for regression.

6. DBA Step-by-Step SPM Approach for Production

Step 1: Confirm Current SQL Plan


SELECT inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       sql_plan_baseline,
       sql_profile,
       sql_patch,
       executions,
       elapsed_time/1000000 elapsed_sec,
       buffer_gets,
       disk_reads
FROM   gv$sql
WHERE  sql_id = '&SQL_ID';

Step 2: Compare Historical Plan Performance


SELECT sql_id,
       plan_hash_value,
       SUM(executions_delta) executions,
       ROUND(SUM(elapsed_time_delta)/1000000/60,2) elapsed_mins,
       ROUND(SUM(cpu_time_delta)/1000000/60,2) cpu_mins,
       SUM(buffer_gets_delta) buffer_gets,
       SUM(disk_reads_delta) disk_reads,
       ROUND(SUM(elapsed_time_delta)/1000000 /
             NULLIF(SUM(executions_delta),0),2) avg_elapsed_sec
FROM   dba_hist_sqlstat
WHERE  sql_id = '&SQL_ID'
GROUP BY sql_id, plan_hash_value
ORDER BY avg_elapsed_sec;

Interpretation:

Result	Decision
One plan only	SPM may not be useful
Multiple plans, one clearly better	SPM candidate
Bad plan has more I/O/buffer gets	SPM candidate
Same plan but runtime worse	Look at volume/waits

Step 3: Display Current Plan


SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
         sql_id => '&SQL_ID',
         cursor_child_no => NULL,
         format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE +ALIAS +NOTE'
       ));

Step 4: Display Historical Plan from AWR


SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_AWR(
         sql_id => '&SQL_ID',
         plan_hash_value => &GOOD_PLAN_HASH_VALUE,
         format => 'ALL +OUTLINE +NOTE'
       ));

Oracle 19c training material covers using DBMS_XPLAN.DISPLAY_AWR to retrieve execution plans from AWR.

Step 5: Check Existing Baseline


SELECT sql_handle,
       plan_name,
       origin,
       enabled,
       accepted,
       fixed,
       autopurge,
       created,
       last_executed,
       sql_text
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%&UNIQUE_SQL_TEXT%';

Step 6: Load Good Plan from Cursor Cache

Use this when the good plan is currently in memory.


DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
                      sql_id          => '&SQL_ID',
                      plan_hash_value => &GOOD_PLAN_HASH_VALUE);
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans_loaded);
END;
/

Step 7: Load Good Plan from AWR

Use this when the good plan is no longer in cursor cache but exists in AWR.


DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_AWR(
                      begin_snap   => &BEGIN_SNAP,
                      end_snap     => &END_SNAP,
                      basic_filter => q'[sql_id = '&SQL_ID']');
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans_loaded);
END;
/

Step 8: Enable / Accept / Fix Baseline Carefully


DECLARE
  l_count PLS_INTEGER;
BEGIN
  l_count := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
               sql_handle      => '&SQL_HANDLE',
               plan_name       => '&PLAN_NAME',
               attribute_name  => 'ENABLED',
               attribute_value => 'YES');

  l_count := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
               sql_handle      => '&SQL_HANDLE',
               plan_name       => '&PLAN_NAME',
               attribute_name  => 'ACCEPTED',
               attribute_value => 'YES');
END;
/

Use FIXED = YES only when you want the optimizer to strongly prefer that fixed baseline. Oracle training material explains that when fixed plans exist, the optimizer considers only those fixed plans and ignores other plans unless they are also fixed.


DECLARE
  l_count PLS_INTEGER;
BEGIN
  l_count := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
               sql_handle      => '&SQL_HANDLE',
               plan_name       => '&PLAN_NAME',
               attribute_name  => 'FIXED',
               attribute_value => 'YES');
END;
/

Step 9: Verify Baseline Usage


SELECT inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       sql_plan_baseline,
       executions,
       elapsed_time/1000000 elapsed_sec
FROM   gv$sql
WHERE  sql_id = '&SQL_ID';

Also check plan note:


SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
         sql_id => '&SQL_ID',
         cursor_child_no => NULL,
         format => 'BASIC +NOTE'
       ));

You should see a note like:


SQL plan baseline "SQL_PLAN_xxxxx" used for this statement

The lab material shows the execution plan note confirming that a SQL plan baseline was used.

7. Practical Scenario-Based Decision Table

Scenario	Use SPM?	Reason	DBA Action
Plan changed after stats gather	Yes	Known good plan can stabilize SQL	Load good plan as baseline
Month-end request took 4 hours instead of 2 minutes due to different plan	Yes	Strong plan regression case	Compare AWR plans and load good plan
SQL slow because of blocking	No	Lock issue, not plan issue	Map blocker to EBS request/user
SQL slow because TEMP full but same plan	Not first	Capacity/data volume issue	Check stats, PGA, TEMP, plan
SQL running serial instead of parallel	Not first	DOP/PX issue	Check `GV$PX_SESSION`, parameters
EBS seeded SQL cannot be modified	Yes	SPM avoids code change	Use baseline/profile/patch carefully
After 19c upgrade	Yes	Prevent optimizer regression	Load critical plans before/after upgrade
New index created, optimizer not using old stable plan	Maybe	Need test which plan is better	Evolve baseline
Bind-sensitive SQL with many child cursors	Maybe	SPM may help but not always	Check ACS and child cursor reasons
Bad SQL due to missing index	Not first	Need design/index review	Tune SQL/index/stats first

8. Production-Safe SPM RCA Wording

Use this for business or management:


During month-end processing, concurrent request <REQUEST_ID> / <PROGRAM_NAME> ran longer than normal.

The request normally completes in <NORMAL_RUNTIME>, but during the issue window it took <ACTUAL_RUNTIME>.

Database analysis showed that SQL_ID <SQL_ID> used plan hash value <BAD_PLAN_HASH_VALUE>, which was different from the previously stable plan hash value <GOOD_PLAN_HASH_VALUE>.

AWR comparison showed that the bad plan consumed higher elapsed time, buffer gets, and disk reads. ASH also showed increased wait on <WAIT_EVENT>.

Root cause:
The issue was caused by SQL execution plan regression, likely triggered by <stats change / optimizer environment change / data volume change / patching / bind variation>.

Corrective action:
The known good plan was validated and considered for SQL Plan Management baseline to stabilize future executions.

Preventive action:
Critical month-end SQL_IDs will be monitored for plan changes before and during month-end, and SQL plan baselines will be reviewed for known high-risk SQLs.

9. My Recommended DBA Approach

For your Oracle EBS 12.2 / 19c month-end support, use this order:


1. Map SQL_ID to request ID and program.
2. Compare normal runtime vs actual runtime.
3. Check current plan hash value.
4. Check AWR historical plan hash values.
5. Identify good plan vs bad plan.
6. Check ASH waits: CPU, I/O, TEMP, blocking, RAC, PX.
7. Confirm the issue is plan regression, not locking or capacity.
8. Check existing baseline/profile/patch.
9. Load good plan into SPM only after validation.
10. Verify baseline usage.
11. Monitor next execution.
12. Document RCA and preventive action.

Final rule:

SPM is best when the SQL was good before, suddenly became bad due to plan change, and you have evidence of a known good plan.

Tuesday, May 12, 2026