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