Migrate SQL Plan Baselines

You can now manually load SQL baseline of a specific sql_id from a different better performing  database  (e.g. SIT) to another database where you have slowness issue for that specific sql or sql_id. 

Oracle notes and most other blogs did not have exact correct steps to execute that. You can  refer this step by step guide.

Migrate or Export / Import SQL Plan Baseline across Database:

In my DEV, environment I had already created  SQL plan baselines for this specific sql_id to stabilize the plan.  In my SIT, the execution plan for the same sql_id was not good. I could not find better execution plans for this specific sql_id either in cursor memory or historical reports in SIT.  Time was running out , hence i decided to get better baseline from DEV and import in SIT to influence optimizer to perform better.   

1. Create staging table in DEV as we want to export the SQL baseline from here and execute the package as SYS or SYSDBA user

exec DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'SQLBASE_SIT', table_owner=>'APPS');

2. Pack the SQL plan baselines  for the sql_id that you want to export from the SQL management base into the staging table.  Execute as SYS user

DECLARE

  my_plans number;

BEGIN

  my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(

    table_name => 'SQLBASE_SIT',

   table_owner=>'APPS',

    plan_name => 'SQL_PLAN_8a6pvqdabksqh4673ac44',

   sql_handle  => 'SQL_851abbb354b962d0');

END;

/

3. Export the table in DEV2 and import it in SIT1

exp system/*** tables='APPS.SQLBASE_SIT' file=sit_sqlbaseline01.dmp log=expsit_sqlbaseline01.log à DEV

imp system/****  file=sit_sqlbaseline01.dmp log=impsqlbase01.log full=y  à SIT

4.  Unpacks the specific sql_handle and plan_name stored in the staging table 'SQLBASE_SIT'. Execute only as SYS user. Importing as SYSTEM user would not work and that's the key. 

DECLARE

  my_plans number;

BEGIN

  my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(

   table_name => 'SQLBASE_SIT',

   table_owner=>'APPS', 
   fixed => 'NO',

   plan_name => 'SQL_PLAN_8a6pvqdabksqh4673ac44',

   sql_handle  => 'SQL_851abbb354b962d0');

END;

/

5. Verify if the sql_id (v$sql ) have picked up sql plan baseline in SIT1 and it has picked up

 select inst_id,parsing_schema_name user2,sql_id,plan_hash_value,executions, trunc(buffer_gets/decode(nvl(executions,1),0,1,nvl(executions,1))) BGE,trunc(ELAPSED_TIME/decode(nvl(executions,1),0,1,nvl(executions,1)))/1000000 EPE, SQL_PLAN_BASELINE from gv$sql where sql_id in ('36qn50vfsgzjh')

order by last_active_time desc;

 INST_ID USER2      SQL_ID        PLAN_HASH_VALUE EXECUTIONS        BGE        EPE SQL_PLAN_BASELINE

------- ---------- ------------- --------------- ---------- ---------- ---------- ------------------------------

      1 APPS       36qn50vfsgzjh      2709963026      38681         25    .000377 SQL_PLAN_8uwa8ckjgdkqh5557b5eb

Sometimes, you may find it odd that optimizer has not picked up the baseline even after importing. In those cases, you can flush the address of sql from shared pool , so that the baselines is picked up. 

- Find the  SQL address & hash_value ( not plan_hash_value)  

select address, hash_value, plan_hash_value, sql_id,executions, fetches,(SUM(elapsed_time)/power(10,6))/SUM(executions) avg_response_in_seconds from gv$sqlarea where sql_id in ('36qn50vfsgzjh','a07y05uya8653') GROUP BY address, hash_value,plan_hash_value,sql_id,executions, fetches;

ADDRESS          HASH_VALUE PLAN_HASH_VALUE SQL_ID        EXECUTIONS    FETCHES AVG_RESPONSE_IN_SECONDS

---------------- ---------- --------------- ------------- ---------- ---------- -----------------------

0700010265D3CD58 3716677168       584524445 36qn50vfsgzjh       6056      11800              .876101928

- Flush the specific SQL from the pool

exec DBMS_SHARED_POOL.PURGE ('0700010265D3CD58,3716677168', 'C');

DROP BASELINE
If there is ever a need to drop the SQL baseline, you can use below

DECLARE 

my_plans number;

BEGIN

my_plans :=DBMS_SPM.DROP_SQL_PLAN_BASELINE (

   plan_name => 'SQL_PLAN_8a6pvqdabksqh4673ac44',

   sql_handle  => 'SQL_851abbb354b962d0');

END;

/

EVOLVE BASELINE
By default the SQL baselines are evolved unless they marked as FIXED. Hence one specific SQL_Handle can have multiple plan_name as they are evolved.  We can run below procedure as SYS or SYSTEM manually and if there are any better plan then baseline plan  that has been evolved then it will be accepted. 

SQL> SET LONG 100000;

SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_da034ca4e16e6345') from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_DA034CA4E16E6345')

--------------------------------------------------------------------------------

  Plan was verified: Time used 7.506 seconds.

  Plan failed performance criterion: 1.25 times better than baseline plan.

                            Baseline Plan      Test Plan       Stats Ratio

                            -------------      ---------       -----------

  Execution Status:              COMPLETE       COMPLETE

  Rows Processed:                       3              3

  Elapsed Time(ms):                 3.316          4.272               .78

  CPU Time(ms):                     2.043          1.891              1.08

  Buffer Gets:                        424            339              1.25

  Physical Read Requests:               0              0

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_DA034CA4E16E6345')

--------------------------------------------------------------------------------

  Physical Write Requests:              0              0

  Physical Read Bytes:                  0              0

  Physical Write Bytes:                 0              0

  Executions:                           1              1

Plan: SQL_PLAN_dn0ucnmhqwsu5e36d0354

------------------------------------

  Plan was verified: Time used 8.31 seconds.

  Plan passed performance criterion: 1.58 times better than baseline plan.

  Plan was changed to an accepted plan.

- Below SQL show that same SQL_handle has 3 Baslines accepted and baseline with best optimizer cost would be picked up. 

select sql_handle, plan_name, enabled, accepted, optimizer_cost from dba_sql_plan_baselines where sql_handle='SQL_da034ca4e16e6345' and accepted='YES'  order by CREATED desc;