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');

OR

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

'tmp/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.