export/import query plan from one database to another using sql tuning set and sql plan baseline

The following three links are my guide documents:

https://carlos-sierra.net/2013/05/02/migrating-an-execution-plan-using-sql-plan-management/

https://aprakash.wordpress.com/2012/07/05/loading-sql-plan-into-spm-using-awr/

https://www.databasejournal.com/features/oracle/article.php/3730391/Oracle-Database-11g-SQL-Plan-Management-Part-2.htm

Option 1: Create SPB on source then migrate SPB into target

Steps:

1. Create SQL Plan Baseline (SPB) in Source
   1. From Memory; or
   2. From AWR (requires Diagnostics Pack license)
2. Package & Export SPB from Source
3. Import & Restore SPB into Target

Pros: Simple

Cons: Generates a SPB in Source system

Option 2: Create SQL Tuning Set (STS) on source, migrate STS into target, promote STS into SPB in target

Steps:

1. Create SQL Tuning Set (STS) in Source (requires Tuning Pack license)
   1. From Memory; or
   2. From AWR (requires Diagnostics Pack license)
2. Package & Export STS from Source
3. Import & Restore STS into Target
4. Create SPB from STS in Target

Pros: No SPB is created in Source system

Cons: Requires license for SQL Tuning Pack

Below are the steps I implement option 2:

exec DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'jsun_sqlset'    );

exec dbms_sqltune.create_sqlset(sqlset_name => 'jsun_sqlset',description => 'sqlset descriptions');

/*

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(                         

         sqlset_name => 'STS_SPM_200'

        ,basic_filter=> q'#sql_text LIKE '%SPM_2_1%' AND parsing_schema_name = 'LDGN'#'

        ,time_limit  => 300

        ,repeat_interval => 5

    ); 

*/    

--Here we would be see how to manually load the plans from AWR to SQL Plan Baseline.To load from AWR we need to create sql tuning set

--Identify the snap_id in which the sql belongs using dba_hist_sqlstat view.Once identified

--select distinct sql_id from v$sql_plan where plan_hash_value='&plan_hash_value'

select min(SNAP_ID),max(SNAP_ID) from DBA_HIST_SQLSTAT where sql_id='&sqlid' or plan_hash_value='&plan_hash_value';

declare

baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;

begin

open baseline_ref_cur for

select VALUE(p) from table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET('jsun_sqlset', baseline_ref_cur);

end;

/

--It would prompt for the begin_snapid , end_snapid and the sql_id for which you want to load the sqlset with. Along with sql_id we can give the specific plan_hash_value in DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY.

--By default, select_workload_repository does not include the SQL Plan so we pass TYPICAL or ALL as the ‘attribute_list’ parameter to get the plan. The default value of BASIC does not capture the plan.

/*

declare

baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;

begin

open baseline_ref_cur for

select VALUE(p) from table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||' and plan_hash_value=1421641795',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET('b8rc6j0krxwdc_sqlset_test', baseline_ref_cur);

end;

/

*/

--View the sqlset using DBA_SQLSET

SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='jsun_sqlset';

--To verify the execution Plan of a SQL_ID in the STS

select * from table(dbms_xplan.display_sqlset('jsun_sqlset','&sql_id'));

--pack the STS

DROP TABLE jsun.sts_staging PURGE;

BEGIN

    DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(

         table_name => 'STS_STAGING'

        ,schema_name => 'JSUN'

        ,tablespace_name => 'USERS'

    );

    DBMS_SQLTUNE.PACK_STGTAB_SQLSET(

         sqlset_name => 'jsun_sqlset'

        ,sqlset_owner => 'JSUN'

        ,staging_table_name => 'STS_STAGING'

        ,staging_schema_owner => 'JSUN'

    );

END;

/

--export the STS table

exp jsun file=saletrn10.dmp tables=jsun.STS_STAGING

--import the STS table

imp jsun file=saletrn10.dmp tables=STS_STAGING fromuser=jsun touser=jsun

--unpack the STS

BEGIN

    DBMS_SQLTUNE.DROP_SQLSET(

        sqlset_name => 'jsun_sqlset'

    );

END;

/

BEGIN

    DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(

         sqlset_name => 'jsun_sqlset'

        ,sqlset_owner => 'JSUN'

        ,replace => TRUE

        ,staging_table_name => 'STS_STAGING'

        ,staging_schema_owner => 'JSUN'

    );

END;

/

SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='jsun_sqlset';

--To load SQL execution plans from SQL set into SQL baseline —

set serveroutput on

declare

my_int pls_integer;

begin

my_int := dbms_spm.load_plans_from_sqlset (

sqlset_name => 'jsun_sqlset',

basic_filter => 'sql_id='||chr(39)||'&sqlid'||chr(39),

sqlset_owner => 'JSUN',

fixed => 'NO',

enabled => 'YES');

DBMS_OUTPUT.PUT_line(my_int);

end;

select count(*) from dba_sql_plan_baselines;

SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE FROM   DBA_SQL_PLAN_BASELINES;

--now run the query and see the plan has been ported.

set autotrace on

set timing on

--run query