Oracle SQL Plan Migration Runbook

Objective

This runbook explains how to move a good execution plan from a source environment (Source Environment) to a Production environment using:

SQL Tuning Set (STS)
Data Pump (expdp/impdp)
SQL Plan Management (SPM)

This approach is commonly used by Oracle Apps DBAs during:

SQL Plan Regression
Month-End Performance Issues
Concurrent Program Slow Performance
Optimizer Plan Changes after Statistics Gathering
Oracle EBS 12.2 Performance Stabilization

High-Level Flow

Identify Good Plan
        ↓
Create SQL Tuning Set (STS)
        ↓
Load Good SQL into STS
        ↓
Create STS Staging Table
        ↓
Export STS Table using Data Pump
        ↓
Transfer Dump File to Production
        ↓
Import STS Table into Production
        ↓
Unpack STS
        ↓
Load Plan into SQL Plan Baseline
        ↓
Purge Old Cursor from Shared Pool
        ↓
Re-execute SQL / Concurrent Program
        ↓
Validate Improved Execution Plan

Source Environment Steps (Source Environment)

Step 1: Identify the Good SQL Plan

Find the SQL_ID and PLAN_HASH_VALUE of the good plan.

SELECT sql_id,
       plan_hash_value
FROM   v$sqlarea
WHERE  sql_id IN ('f9k42ab71mn8q');

SELECT sql_id,
       plan_hash_value
FROM   v$sql
WHERE  sql_id IN ('f9k42ab71mn8q');

Step 2: Create Empty SQL Tuning Set (STS)

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
      sqlset_name => 'f9k42ab71mn8q_STS',
      description => 'STS to move better plan to Production');
END;
/

Step 3: Load SQL Information into STS

DECLARE
  s_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN

  OPEN s_sqlarea_cursor FOR
  SELECT VALUE(p)
  FROM TABLE(
       DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
       'sql_id = ''f9k42ab71mn8q''
        AND plan_hash_value = 1847263512')) p;

  DBMS_SQLTUNE.LOAD_SQLSET(
      sqlset_name     => 'f9k42ab71mn8q_STS',
      populate_cursor => s_sqlarea_cursor);

END;
/

Step 4: Verify SQL Tuning Set Contents

SELECT name,
       statement_count,
       description
FROM   dba_sqlset;

Formatting commands:

COLUMN sql_text FORMAT a30
COLUMN sch FORMAT a3
COLUMN elapsed FORMAT 999999999

Query STS contents:

SELECT sql_id,
       parsing_schema_name AS "SCH",
       sql_text,
       elapsed_time AS "ELAPSED",
       buffer_gets
FROM TABLE(
     DBMS_SQLTUNE.SELECT_SQLSET('f9k42ab71mn8q_STS'));

Detailed verification:

SELECT *
FROM TABLE(
     DBMS_SQLTUNE.SELECT_SQLSET('f9k42ab71mn8q_STS'));

Step 5: Create Staging Table

Note: Table names and parameters are case-sensitive.

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
     table_name => 'TEST');

Step 6: Pack STS into Staging Table

BEGIN

  DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
      sqlset_name         => 'f9k42ab71mn8q_STS',
      sqlset_owner        => 'SYSTEM',
      staging_table_name  => 'TEST',
      staging_schema_owner=> 'SYSTEM');

END;
/

Step 7: Create Oracle Directory Object

CREATE DIRECTORY TEST AS
'/opt/mis/ebs_backup_lv/backup_1/EBS_SQLSET_BACKUP';

Step 8: Export Staging Table using Data Pump

expdp system DIRECTORY=TEST \
DUMPFILE=f9k42ab71mn8q_STS.dmp \
TABLES=TEST

Step 9: Transfer Dump File to Production

Transfer the dump file using:

SCP
FTP
SFTP

Example:

scp f9k42ab71mn8q_STS.dmp oracle@targetserver:/backup

Target Environment Steps (Production)

Step 10: Import the Dump File

impdp system DIRECTORY=TEST \
DUMPFILE=f9k42ab71mn8q_STS.dmp \
TABLES=TEST

Step 11: Unpack SQL Tuning Set

BEGIN

  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
      sqlset_name          => '%',
      sqlset_owner         => 'SYSTEM',
      replace              => TRUE,
      staging_table_name   => 'TEST',
      staging_schema_owner => 'SYSTEM');

END;
/

Step 12: Load Plan from STS into SQL Plan Baseline

VARIABLE v_plan_cnt NUMBER

EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
         sqlset_name => 'f9k42ab71mn8q_STS',
         sqlset_owner => 'SYSTEM',
         basic_filter =>
         'sql_id = ''f9k42ab71mn8q''
          AND plan_hash_value = 1847263512');

Step 13: Purge Existing SQL from Shared Pool

This forces Oracle to re-parse and pick the new SQL Plan Baseline.

Generate purge command:

SELECT 'exec DBMS_SHARED_POOL.PURGE('''
       || ADDRESS || ',' || HASH_VALUE || ''',''C'');'
FROM   v$sqlarea
WHERE  sql_id IN ('f9k42ab71mn8q');

Execute generated command:

EXEC DBMS_SHARED_POOL.PURGE(
'0000001006B396C8,2113289046',
'C');

Step 14: Alternative Method - Load from Cursor Cache

DECLARE
  i NATURAL;
BEGIN

  i := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
       'f9k42ab71mn8q',
       1847263512);

END;
/

Validation Queries

Check SQL Baselines

SELECT sql_handle,
       plan_name,
       enabled,
       accepted,
       fixed
FROM   dba_sql_plan_baselines
WHERE  signature IN (
       SELECT exact_matching_signature
       FROM   v$sql
       WHERE  sql_id='f9k42ab71mn8q');

Verify Active Plan

SELECT sql_id,
       child_number,
       plan_hash_value,
       executions,
       elapsed_time
FROM   v$sql
WHERE  sql_id='f9k42ab71mn8q';

Check if Baseline is Used

SELECT sql_id,
       sql_plan_baseline,
       plan_hash_value
FROM   v$sql
WHERE  sql_id='f9k42ab71mn8q';

Oracle Apps DBA Production Checklist

Check	Status
Good SQL_ID identified	☐
Correct PLAN_HASH_VALUE captured	☐
STS created successfully	☐
SQL loaded into STS	☐
Staging table created	☐
STS packed successfully	☐
Data Pump export completed	☐
Dump transferred securely	☐
Import completed in Production	☐
STS unpacked successfully	☐
SQL Plan Baseline loaded	☐
Old cursor purged	☐
Concurrent request rerun	☐
Improved performance validated	☐

Real-Time Oracle EBS Scenario

Problem

A month-end concurrent request that normally completes in 2 minutes suddenly started taking 4 hours after statistics gathering.

Root Cause

Optimizer selected a new bad execution plan with:

Full Table Scan
High Logical Reads
Excessive Nested Loop Operations
Large TEMP Usage

Solution

DBA identified a good historical plan from Source Environment and migrated it to Production using:

SQL Tuning Set (STS)
SQL Plan Baseline (SPM)

Result

Before	After
Runtime: 4 Hours	Runtime: 2 Minutes
TEMP Spike	Stable TEMP
CPU High	CPU Normal
Business Delay	Business Success

Important Notes

Best Practices

Always validate the plan in lower environments first.
Never purge shared pool aggressively in peak production hours.
Take business approval before rerunning concurrent requests.
Verify plan stability after stats gathering.
Monitor AWR and ASH after implementation.

Important DBA Views

View	Purpose
V$SQL	Active SQL details
V$SQLAREA	Aggregated SQL statistics
DBA_SQLSET	SQL Tuning Sets
DBA_SQL_PLAN_BASELINES	SQL Baselines
V$SQL_MONITOR	Real-time SQL monitoring
DBA_HIST_SQLSTAT	Historical SQL statistics
DBA_HIST_ACTIVE_SESS_HISTORY	ASH performance analysis

Conclusion

Using SQL Plan Management (SPM) and SQL Tuning Sets (STS) is one of the safest methods to stabilize SQL performance in Oracle EBS 12.2 Production environments.

This approach helps Oracle Apps DBAs:

Avoid risky code changes
Restore performance quickly
Reduce month-end failures
Stabilize execution plans
Improve business confidence

It is a critical real-world DBA skill for handling production SQL regressions.

Oracle Apps DBA stuff

Friday, May 15, 2026

plan migration