Transport Good Execution Plan(Hash Plan) from one database to another database

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;

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; /

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,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; /

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; /

6. Export The table

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

7. Import of the Staging table

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; /

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' );

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');

11. To verify the Baseline created in the database

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

12. Drop SQL tuning Set from Source

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

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011