How to Migrate SQL Plan Baselines Between Oracle Databases

After database upgrading / Patching, or database is migrated onto new infrastructure, it is common to see database performance is degrading due to SOL execution plans changed.

What we can do is by creating  SQL plan baselines from old database, then copied them on to new database.

1) Identify the SQL plan baseline(s) to be migrated from source database:

SQL> select sql_handle, plan_name, sql_text, enabled, accepted 
     from   dba_sql_plan_baselines 
     where enabled='YES' 
       and accepted='YES' 
       and PLAN_NAME='SQL_PLAN_7ns0utmam6vc64a63fd18';

SQL_HANDLE           PLAN_NAME                      SQL_TEXT ENA ACC
-------------------- ------------------------------ -------- --- ---
SQL_7a601accd5336d86 SQL_PLAN_7ns0utmam6vc64a63fd18 UPDATE . YES YES

2) Create a staging table on source database:

SQL> BEGIN
        DBMS_SPM.CREATE_STGTAB_BASELINE(
        table_name => 'spm_tab',
        table_owner => 'testuser',
        tablespace_name => 'USERS');
END; 

/

PL/SQL procedure successfully completed.


SQL> select table_name,owner from dba_tables where owner='TETSUSER';

TABLE_NAME   OWNER
------------ ---------
SPM_TAB      TETSUSER

3) Pack  SQL plan baseline(s) into above staging table ( spm_tab):

var xx number;
begin
:xx := DBMS_SPM.PACK_STGTAB_BASELINE('spm_tab', 'testuser', -
      sql_handle => 'SQL_7a601accd5336d86',  -
      plan_name =>  'SQL_PLAN_7ns0utmam6vc64a63fd18' ); 
end;

/

PL/SQL procedure successfully completed.

4) Export staging table with datapump:

$ expdp directory=exp_dir dumpfile=testuser_spm_tab.dmp 
  tables=testuser.spm_tab

5) Transfer the datapump file onto target database server.

6) Import staging table onto target database:

$ impdp directory=imp_dir dumpfile=testuser_spm_tab.dmp 
tables=testuser.spm_tab remap_schema=... remap_tablespace=...

7) Unpack the SQL plan baselines in staging table into target database:

SQL> var xx number;
     begin
      :xx := DBMS_SPM.UNPACK_STGTAB_BASELINE('SPM_TAB', 'TESTUSER');
     end;

     /

PL/SQL procedure successfully completed.

8) Verify the SQL plan baselines have been migrated onto target database:

SQL>select sql_handle, plan_name, sql_text, enabled, accepted, fixed 
      from dba_sql_plan_baselines
      where SQL_HANDLE='SQL_7a601accd5336d86';

SQL_HANDLE           PLAN_NAME                    SQL_TEXT  ENA ACC FIX
-------------------- ------------------------------ ------- --- --- ---
SQL_7a601accd5336d86 SQL_PLAN_7ns0utmam6vc64a63fd18 UPDATE  YES YES NO

9) Display execution plans for this SQL handle of a SQL plan baseline:

SQL> set linesize 120
SQL> set pagesize 2000
SQL> select * from TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE( -
     'SQL_7a601accd5336d86','SQL_PLAN_7ns0utmam6vc64a63fd18'));

Syntax:

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
   sql_handle      IN VARCHAR2 := NULL,
   plan_name       IN VARCHAR2 := NULL,
   format          IN VARCHAR2 := 'TYPICAL')
 RETURN dbms_xplan_type_table;

Parameter	Description
sql_handle	SQL statement handle. It identifies a SQL statement whose plan(s) are to be displayed.
plan_name	Plan name. It identifies a specific plan. Default NULL means all plans associated with identified SQL statement are explained and displayed.
format	Format string determines what information stored in the plan displayed. One of three format values (‘BASIC’, ‘TYPICAL’, ‘ALL’) can be used, each representing a common use case.