Every DBA eventually meets this scenario: a query runs beautifully in UAT but picks a terrible execution plan in Production. The data is comparable, the code is identical, yet the optimizer disagrees with itself across environments. Rather than hinting the SQL (application change), locking statistics (broad side effects), or gambling on a SQL Profile, the cleanest supported fix is often to transport the known-good plan itself using a SQL Tuning Set (STS) and enforce it with SQL Plan Management (SPM).
This post is a production-hardened, end-to-end runbook. Replace the placeholders <SQL_ID>, <GOOD_PLAN_HASH>, and the schema/paths with your values. Tested approach applies to Oracle 11.2 through 19c and works unchanged in Oracle E-Business Suite environments.
Workflow at a Glance
- Capture the optimal plan into a SQL Tuning Set on the source database.
- Pack the STS into a staging table.
- Export and transfer the staging table with Data Pump.
- Import and unpack the STS on the target database.
- Load the plan as an accepted SQL Plan Baseline.
- Purge the bad cursor from the shared pool.
- Verify the baseline is actually being used — the step most runbooks forget.
Prerequisites & Pre-Checks
- Privileges:
ADMINISTER SQL TUNING SET(or DBA) on both databases; Data Pump export/import privileges. - Working schema: Use a regular administrative schema (shown here as
DBADMIN) for the staging table. Oracle explicitly recommends not staging inSYS, and keepingSYSTEMclean is good hygiene. - Baselines must be enabled on target. If this parameter is
FALSE, everything below succeeds silently and changes nothing:
SHOW PARAMETER optimizer_use_sql_plan_baselines -- must be TRUE
- Plan reproducibility: the target must have the same indexes, comparable statistics, and a compatible optimizer environment. A baseline is a request, not a command — if the optimizer cannot reproduce the plan (missing index, dropped partition), it silently ignores the baseline. This is why the verification phase at the end is mandatory.
Phase 1 — Source Database: Capture and Export
Step 1: Identify the Good Plan
SELECT sql_id, plan_hash_value, executions,
ROUND(elapsed_time/DECODE(executions,0,1,executions)/1e6,3) avg_elapsed_sec
FROM v$sql
WHERE sql_id = '<SQL_ID>';
Note the PLAN_HASH_VALUE of the efficient plan — you will filter on it at every subsequent step so that only the good plan travels, never the bad one.
Step 2: Create and Populate the SQL Tuning Set
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name => 'MIGRATE_PLAN_STS',
description => 'Transfer optimal plan for <SQL_ID> to production'
);
END;
/
DECLARE
c_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN c_cur FOR
SELECT VALUE(p)
FROM TABLE(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'sql_id = ''<SQL_ID>'' AND plan_hash_value = <GOOD_PLAN_HASH>'
)
) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'MIGRATE_PLAN_STS',
populate_cursor => c_cur
);
END;
/
Verify the STS contains exactly what you expect — one statement, one plan:
SELECT sql_id, parsing_schema_name, plan_hash_value, elapsed_time, buffer_gets
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('MIGRATE_PLAN_STS'));
Step 3: Pack the STS into a Staging Table
Pass the schema explicitly to CREATE_STGTAB_SQLSET and keep it consistent with staging_schema_owner in the pack call — a mismatch here is the classic cause of ORA-19381: staging table does not exist.
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
table_name => 'STS_STAGING_TAB',
schema_name => 'DBADMIN'
);
END;
/
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
sqlset_name => 'MIGRATE_PLAN_STS',
sqlset_owner => USER,
staging_table_name => 'STS_STAGING_TAB',
staging_schema_owner => 'DBADMIN'
);
END;
/
Step 4: Export and Transfer
Do not create or replace DATA_PUMP_DIR — it already exists in every database and repointing the default is a bad habit. Use a purpose-built directory object:
CREATE DIRECTORY STS_MIG_DIR AS '/u01/exports/sts_migration';
-- OS command:
expdp dbadmin DIRECTORY=STS_MIG_DIR DUMPFILE=migrate_plan_sts.dmp \
LOGFILE=migrate_plan_sts_exp.log TABLES=DBADMIN.STS_STAGING_TAB
scp /u01/exports/sts_migration/migrate_plan_sts.dmp \
oracle@target_host:/u01/imports/sts_migration/
Phase 2 — Target Database: Import and Enforce
Step 5: Import the Staging Table
CREATE DIRECTORY STS_MIG_DIR AS '/u01/imports/sts_migration';
-- OS command (add REMAP_SCHEMA if the schema differs on target):
impdp dbadmin DIRECTORY=STS_MIG_DIR DUMPFILE=migrate_plan_sts.dmp \
LOGFILE=migrate_plan_sts_imp.log TABLES=DBADMIN.STS_STAGING_TAB
Step 6: Unpack the SQL Tuning Set
Name the STS explicitly rather than using the '%' wildcard — you want deliberate, auditable actions in production, and replace => TRUE combined with a wildcard can silently overwrite unrelated tuning sets.
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
sqlset_name => 'MIGRATE_PLAN_STS',
sqlset_owner => '%',
replace => TRUE,
staging_table_name => 'STS_STAGING_TAB',
staging_schema_owner => 'DBADMIN'
);
END;
/
Step 7: Load the Plan as an Accepted Baseline
VARIABLE v_plan_cnt NUMBER;
BEGIN
:v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'MIGRATE_PLAN_STS',
sqlset_owner => 'DBADMIN',
basic_filter => 'sql_id = ''<SQL_ID>'' AND plan_hash_value = <GOOD_PLAN_HASH>'
);
END;
/
PRINT v_plan_cnt
-- MUST be >= 1. Zero means the filter matched nothing
-- (typo in sql_id / plan_hash) — stop and investigate.
Confirm the baseline exists, is enabled, and is accepted:
SELECT sql_handle, plan_name, enabled, accepted, fixed, origin FROM dba_sql_plan_baselines WHERE created > SYSDATE - 1/24 ORDER BY created DESC;
Optional — pin the plan: a loaded baseline is ACCEPTED but not FIXED. If you want to prevent future auto-evolved plans from competing with it, fix it:
DECLARE
n PLS_INTEGER;
BEGIN
n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => '<SQL_HANDLE>',
plan_name => '<PLAN_NAME>',
attribute_name => 'fixed',
attribute_value => 'YES');
END;
/
Step 8: Purge the Bad Cursor from the Shared Pool
An existing cursor is not invalidated by a new baseline, so the bad plan keeps executing until it ages out. Purge it. On RAC, the purge is instance-local — generate and run the command on every instance:
SELECT inst_id,
'EXEC DBMS_SHARED_POOL.PURGE ('''||address||','||hash_value||''', ''C'');' purge_cmd
FROM gv$sqlarea
WHERE sql_id = '<SQL_ID>';
-- Run the generated command connected to each instance listed:
EXEC DBMS_SHARED_POOL.PURGE ('<ADDRESS>,<HASH_VALUE>', 'C');
Step 9: Verify the Baseline Is Actually Used
This is the step that separates a runbook from a hope. Have the application (or a test harness with the same binds) execute the statement, then check:
SELECT sql_id, child_number, plan_hash_value, sql_plan_baseline
FROM v$sql
WHERE sql_id = '<SQL_ID>';
-- SQL_PLAN_BASELINE must be non-null and
-- PLAN_HASH_VALUE must equal <GOOD_PLAN_HASH>
-- Full plan detail:
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle => '<SQL_HANDLE>',
format => 'BASIC NOTE'));
If SQL_PLAN_BASELINE stays null, the optimizer could not reproduce the plan on the target — go back and compare indexes, statistics, and optimizer parameters between the environments before anything else.
Practical Notes
- Shortcut when the good plan already exists in the target's cursor cache (e.g., it ran well last week before a stats change): skip the entire STS transfer and load directly:
DECLARE n PLS_INTEGER; BEGIN n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE('<SQL_ID>', <GOOD_PLAN_HASH>); DBMS_OUTPUT.PUT_LINE('Plans loaded: '||n); END; / - Single-statement alternative: for a one-off transfer of a single SQL_ID, Oracle's
coe_xfr_sql_profile.sql(bundled with SQLT, MOS Doc ID 1955195.1) generates a self-contained script on the source that you simply execute on the target — no Data Pump, no staging table. Use STS/SPM when you want a genuine baseline with evolution history, or when moving multiple statements. - Bind-sensitive SQL: if the statement is bind-aware (check
V$SQL.IS_BIND_AWARE), pinning a single plan can penalize other bind value sets. Confirm the good plan is good across representative binds before fixing it. - Cleanup: once verified, drop the staging tables on both sides and, if no longer needed, the STS (
DBMS_SQLTUNE.DROP_SQLSET) to keep environments tidy.
Summary
STS + SPM is the supported, code-change-free way to move a proven execution plan between databases: capture with a plan-hash filter, pack, transport, unpack, load as an accepted baseline, purge the stale cursor on every instance, and — always — verify that V$SQL.SQL_PLAN_BASELINE lights up. The transfer mechanics are easy; the discipline is in the pre-checks (baselines enabled, matching objects) and the post-check (plan reproduction). Skip those and you have a baseline in the dictionary and the same bad plan in production.
