How to migrate SQL PLAN BASELINE from one database to another

We can migrate execution plan for a particular query from one database to another . This article will provide all the details on how to migrate and when to migrate the plan of a query

When to migrate the plan of a query :-

We came across with this kind of scenario when a query is running fine in pre prod/UAT and is not running properly in production.

Everything related to query in both the environments are same, Plan which the query is using in production is not good enough and taking a lot of time.

Best approach in this kind of scenario is to purge the bad plan from the shared pool . PFB check here to purge a SQL PLAN from shared pool :-

SQL> exec DBMS_SHARED_POOL.PURGE ('0000000410E16308, 3635099784', 'C');

PL/SQL procedure successfully completed.

Now when we run the query again in production , it will go for a hard parse and Oracle will automatically select the best execution plan.

What if , execution plan which Oracle selected is not that good as it's in UAT/Preprod, then we will decide to migrate the plan from UAT/Pre prod to production.

How to migrate the plan of a query to another database :-

gather the sql_id and plan_hash_value for the query , we an get this value from gv$sqlarea

select sql_id,plan_hash_value, sql_text from gv$sql_area where sql_text like '%

For this article :-

Sql_id = '45g4pghgh9rt'

Plan_Hash_value :- 2904123173

1. Load the plan from shared pool

DECLARE

my_plans pls_integer;

BEGIN

my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id =>'45g4pghgh9rt', plan_hash_value=>'2904123173');

end;

/

PL/SQL procedure successfully completed.

2. Check the details of the baseline in dba_sql_plan_baselines :-

select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,fixed,to_char(last_executed,'dd-mon-yy HH24:MI') last_executed from dba_sql_plan_baselines;

3. Create a staging table, can’t create in sys schema :-

SQL> exec DBMS_SPM.CREATE_STGTAB_BASELINE('STGTAB', 'AMIT');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16

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

TABLE_NAME                     OWNER

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

STGTAB                         AMIT

Elapsed: 00:00:00.07

4. Pack the baseline for the sql in the table :-

SQL>  var x number;

begin

:x := DBMS_SPM.PACK_STGTAB_BASELINE('STGTAB', 'AMIT', sql_handle => 'SQL_27c1b5a44008ee73', plan_name => 'SQL_PLAN_2ghdpnj00jvmm46bd3d4a' );

end;SQL>   2    3

  4  /

PL/SQL procedure successfully completed.

5. Take export backup of STGTAB table and import it to the target table and then unpack the baseline

6. Unpack the baseline in target database, before this purge the previous/bad sql plan available in target database.

SQL> var x number;

begin

:x := DBMS_SPM.UNPACK_STGTAB_BASELINE('STGTAB', 'AMIT');

end;SQL>   2    3

  4  /

PL/SQL procedure successfully completed.

7. Sqlplan baseline migration done, you will see the plan hash value for respective sql will be changed.