How to copy a BEST Execution Plan for any SQL ID from One Database to Another Database in Oracle

Scenario-1:

Assume, you have a SQL, which is using a better plan on one environment(assume pre-prod) 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.

Scenario-2:

Assume, you have a SQL, which is using a better plan on pre-prod after doing lots of tunning activities. May be you used SQL-Patch etc but when you want to move the same to production with new code deployment plan, then how you can achieve this?

Let us go with actual steps what is tested successfully.

Points to remember:

Your database code and data must be same with structure-wise but data volume may be similar may not be exact.

[From Source Database]

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 ('fkmu4ubzmdx629');
SQL> select sql_id, plan_hash_value from v$sql 
     WHERE sql_id IN ('fkmu4ubzmdx629');

2. Create a SQL Tuning Set 

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 => 'fkmu4ubzmdx629_STS'
, description => 'fkmu4ubzmdx629 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 = ''fkmu4ubzmdx629'' AND plan_hash_value = 367652949 ')) p;
   -- load the tuning set
  DBMS_SQLTUNE.LOAD_SQLSET (sqlset_name => 'fkmu4ubzmdx629_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('fkmu4ubzmdx629_STS' ));

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

3. 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 => 'FKMU4UBZMDX629_STS_TBL', schema_name => 'SYSTEM');

END;

/

4. 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 => 'fkmu4ubzmdx629_STS'
, sqlset_owner => 'SYS'
, staging_table_name => 'FKMU4UBZMDX629_STS_TBL'
, staging_schema_owner => 'SYSTEM'
);
END;

/

5. Export the table using datapump utility

expdp system DIRECTORY=data_pump_dir DUMPFILE=fkmu4ubzmdx629_STS_Plan.dmp TABLES=FKMU4UBZMDX629_STS_TBL

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

[On Target Server]

1. Import into The destination Database

impdp system DIRECTORY=data_pump_dir DUMPFILE=fkmu4ubzmdx629_STS.dmp TABLES=FKMU4UBZMDX629_STS_TBL

2. 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 => 'FKMU4UBZMDX629_STS_TBL'
, staging_schema_owner => 'SYSTEM');
END;
/

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

begin

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

end;

/

4. 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 ('fkmu4ubzmdx629');

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