1. Create Empty Tuning Set

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET (
    sqlset_name  => 'SQLT_WKLD_STS', 
    description  => 'STS to store SQL from the private SQL area' 
);
END;

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET (
    sqlset_name  => 'SQLT_WKLD_STS', 
    description  => 'STS to store SQL from the private SQL area' 
);
END;
/

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 = ''2t8gdjy0pbs57'' AND plan_hash_value = ''618955923'' ')  ) p;
-- load the tuning set
  DBMS_SQLTUNE.LOAD_SQLSET (  
    sqlset_name     => 'SQLT_WKLD_STS'
,   populate_cursor =>  c_sqlarea_cursor 
);
END;
/

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 = ''2t8gdjy0pbs57'' AND plan_hash_value = ''618955923'' ')  ) p;
-- load the tuning set
  DBMS_SQLTUNE.LOAD_SQLSET (  
    sqlset_name     => 'SQLT_WKLD_STS'
,   populate_cursor =>  c_sqlarea_cursor 
);
END;
/

3. Display the content of Sql Tuning set

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


SQL_ID        SCH                       SQL_TEXT                                                                            ELAPSED BUFFER_GETS
------------- ------------------------- -------------------------------------------------------------------------------- ---------- -----------
2t8gdjy0pbs57 TESTUSER      INSERT /*+ APPEND */ INTO W_L_T.PROJECT as SELECT…..   24844903     2577896

SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT, ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_WKLD_STS' ) );
 
 
SQL_ID        SCH                       SQL_TEXT                                                                            ELAPSED BUFFER_GETS
------------- ------------------------- -------------------------------------------------------------------------------- ---------- -----------
2t8gdjy0pbs57 TESTUSER      INSERT /*+ APPEND */ INTO W_L_T.PROJECT as SELECT…..   24844903     2577896
SELECT sql_id, parsing_schema_name as "SCH", sql_text,buffer_gets as "B_GETS",disk_reads, ROUND(disk_reads/buffer_gets*100,2) "%_DISK" FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET(  'SQLT_WKLD_STS', '(disk_reads/buffer_gets) >= 0.50' ) );

SQL_ID        SCH                       SQL_TEXT                                                                             B_GETS DISK_READS     %_DISK
------------- ------------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------
2t8gdjy0pbs57 TESTUSER   INSERT /*+ APPEND */ INTO W_L_T.PROJECT as SELECT…..      2577896    2435837      94.49

SELECT sql_id, parsing_schema_name as "SCH", sql_text,buffer_gets as "B_GETS",disk_reads, ROUND(disk_reads/buffer_gets*100,2) "%_DISK" FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET(  'SQLT_WKLD_STS', '(disk_reads/buffer_gets) >= 0.50' ) );
 
SQL_ID        SCH                       SQL_TEXT                                                                             B_GETS DISK_READS     %_DISK
------------- ------------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------
2t8gdjy0pbs57 TESTUSER   INSERT /*+ APPEND */ INTO W_L_T.PROJECT as SELECT…..      2577896    2435837      94.49

4. Create a Staging table to hold the exported SQL Tuning set
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (table_name => 'MY_TUNING_SET', schema_name => 'PDBADMIN');
END;
/

BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (table_name => 'MY_TUNING_SET', schema_name => 'PDBADMIN');
END;
/

5. Load the SQL Tuning Set information to the Staging Table

BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
sqlset_name => 'SQLT_WKLD_STS',
 sqlset_owner => 'PDBADMIN',
 staging_table_name => 'MY_TUNING_SET', 
 staging_schema_owner => 'PDBADMIN'
);
END;
/

BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
sqlset_name => 'SQLT_WKLD_STS',
 sqlset_owner => 'PDBADMIN',
 staging_table_name => 'MY_TUNING_SET', 
 staging_schema_owner => 'PDBADMIN'
);
END;
/

6. Export of The table

source ~/.bash_profile; nohup expdp userid="USERNAME/PASSWORD@lnx01.oraclevcn.com:1521/PDB1S001.oraclevcn.com" parfile/u01/dumps/mytunigset_table.par &

source ~/.bash_profile; nohup expdp userid="USERNAME/PASSWORD@lnx01.oraclevcn.com:1521/PDB1S001.oraclevcn.com" parfile/u01/dumps/mytunigset_table.par &
7. Import of the Staging table
source ~/.bash_profile; nohup impdp userid="USERNAME/PASSWORD@lnx02.oraclevcn.com:1521/PDB1P001.oraclevcn.com" parfile/u01/dumps/mytunigset_table.par &

source ~/.bash_profile; nohup impdp userid="USERNAME/PASSWORD@lnx02.oraclevcn.com:1521/PDB1P001.oraclevcn.com" parfile/u01/dumps/mytunigset_table.par &

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

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

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

9. 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 => 'SQLT_WKLD_STS', -
sqlset_owner => 'PDBADMIN', -
basic_filter => 'sql_id = ''2t8gdjy0pbs57'' AND plan_hash_value = 618955923' );

VARIABLE v_plan_cnt NUMBER
EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
sqlset_name => 'SQLT_WKLD_STS', -
sqlset_owner => 'PDBADMIN', -
basic_filter => 'sql_id = ''2t8gdjy0pbs57'' AND plan_hash_value = 618955923' );
10. 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 ('2t8gdjy0pbs57');
exec SYS.DBMS_SHARED_POOL.PURGE ('0000001006B396C8, 2113289046', 'C');

select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');' from V$SQLAREA where SQL_ID in ('2t8gdjy0pbs57');
exec SYS.DBMS_SHARED_POOL.PURGE ('0000001006B396C8, 2113289046', 'C');

11. To verify Baseline created in the database

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;


12. Drop SQL tunung Set from Source

BEGIN
DBMS_SQLTUNE.drop_sqlset (sqlset_name => '2t8gdjy0pbs57_STS');
END;

BEGIN
DBMS_SQLTUNE.drop_sqlset (sqlset_name => '2t8gdjy0pbs57_STS');
END;