About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Wednesday, December 14, 2016

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.

I hope this article helped you

Thanks
Amit Rath

6 comments:

  1. Can I migrate a baseline from 11.2.0.4 to an Oracle 12 database? Being able to do so for our Ellucian (Banner) ERP could be one strategy to handle performance issues related to Oracle 12 migration. Many customers have had new performance opportunities crop up. Thank you. Rick Draper Youngstown State University

    ReplyDelete
  2. Amit, we have a monster size SQL that runs in 15 min in Stage. This sql would not move an inch after 6 hrs of execution in Prod, a bigger machine. We could not convince developers to tune sql since it was working fine in stage. Your technique saved our day by bringing baseline from stage.

    ReplyDelete
    Replies
    1. Great to know that it helped your case. Thanks for writing in.

      Delete
  3. i want to migrate all the execution plans of all sql queries from 1 database onto another. How to achieve that?

    ReplyDelete
  4. Im facing issues in unpacking the baseline, Im migrating the plan from 11.2.0.3 to 12.2.0.1.
    These are the errors im getting right now
    ORA-38172: No SQL management object satisfies specified filters
    ORA-06512: at "SYS.DBMS_SMB", line 1033
    ORA-06512: at "SYS.DBMS_SPM", line 3062
    Can you please suggest something

    ReplyDelete
  5. Thanks a lot It Helped a lot.

    ReplyDelete