How to copy a specific Execution Plan for any SQL ID from One Server to Another Database

Let us say, you have a SQL, which is using a better plan on one environment and not-so-good plan in another environment. And you want to copy the better plan to the destination database and force the optimizer to use this plan.

[On Source DB]
1. Get the SQL ID, Plan Hash Value of the good Plan. You can use any of the following SQL.

SQL> select sql_id, plan_hash_value from v$sqlarea 
     WHERE sql_id IN ('fsy3ubymdz500');

SQL> select sql_id, plan_hash_value from v$sql 
     WHERE sql_id IN ('fsy3ubymdz500');

2. Create a SQL Tuning Set for the above SQL Id and Plan Hash Value. This Tuning Set will be copied to the Destination database using a staging table and the datapump utility.

Step 1 : Create an Empty SQL Tuning Set

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name => 'fsy3ubymdz500_STS'
, description => 'fsy3ubymdz500 STS to copy to Prod - better plan'
);
END;
/

Step 2 : Load the SQL Information for the SQL ID into this Tuning Set

DECLARE
 c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR
  SELECT VALUE(p)
    FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''fsy3ubymdz500'' AND plan_hash_value = 367652949 ')) p;

  -- load the tuning set
  DBMS_SQLTUNE.LOAD_SQLSET (sqlset_name => 'fsy3ubymdz500_STS', populate_cursor => c_sqlarea_cursor);
END;
/

** You can display the content of SQL Tuning Set using :
SELECT NAME, STATEMENT_COUNT, DESCRIPTION FROM DBA_SQLSET;

COLUMN SQL_TEXT FORMAT a30
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999

SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT,
ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'fsy3ubymdz500_STS' ) );

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


2. Create a Staging table to hold the exported SQL Tuning set

--> Table name and other parameters are case-sensitive  <--
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (table_name => 'FSY3UBYMDZ500_STS_TBL', schema_name => 'SYSTEM');
END;
/

2. Load the SQL Tuning Set information to the Staging Table. (See Above on how to create and populate a SQL Tuning Set)

BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
sqlset_name => 'fsy3ubymdz500_STS'
, sqlset_owner => 'SYS'
, staging_table_name => 'FSY3UBYMDZ500_STS_TBL'
, staging_schema_owner => 'SYSTEM'
);
END;
/

3. Export the table using datapump utility

expdp system DIRECTORY=data_pump_dir DUMPFILE=fsy3ubymdz500_STS.dmp TABLES=FSY3UBYMDZ500_STS_TBL

4. Transfer the dump file to the destination server. (Using scp or ftp command)

[On Target Server]
5. Import into The destination Database

impdp system DIRECTORY=data_pump_dir DUMPFILE=fsy3ubymdz500_STS.dmp TABLES=fsy3ubymdz500_STS_TBL

6. Unpack The SQL Tuning set from the staging table to the destination server

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
sqlset_name => '%'
, sqlset_owner => 'SYS'
, replace => true
, staging_table_name => 'FSY3UBYMDZ500_STS_TBL'
, staging_schema_owner => 'SYSTEM');
END;
/

7. Load the plan from SQL Tuning Set to SQL Plan Baseline.

VARIABLE v_plan_cnt NUMBER
EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
sqlset_name => 'fsy3ubymdz500_STS', -
sqlset_owner => 'SYS', -
basic_filter => 'sql_id = ''fsy3ubymdz500'' AND plan_hash_value = 367652949' );


8. Flush the two SQLs from the shared pool, so that the optimizer will pick the new plan.

select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');'
from V$SQLAREA where SQL_ID in ('fsy3ubymdz500');

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