{"id":7974,"date":"2019-03-11T15:03:51","date_gmt":"2019-03-11T15:03:51","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/transfer-sql-plans-by-using-a-sql-plan-baseline\/"},"modified":"2026-11-03T19:29:11","modified_gmt":"2026-11-03T19:29:11","slug":"transfer-sql-plans-by-using-a-sql-plan-baseline","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/transfer-sql-plans-by-using-a-sql-plan-baseline\/","title":{"rendered":"Transfer SQL plans by using a SQL plan baseline"},"content":{"rendered":"

Introduction to SQL Plan Management<\/strong><\/p>\n

A particular SQL query might perform poorly on one database (such as production) but work fine on another database (such as development). This situation might occur if the same query has a different execution plan on each instance. This
\nblog shows how to transfer the execution plan from an instance, where query works fine, to another instance, where query behaves poorly, by using the SQL plan baseline feature that Oracle\u00a9 Database\u00a9 first introduced in version 11g.\u00a9  <\/p>\n

Oracle SQL Plan Management (SPM) is a feature in Oracle Database that captures all the historical execution plans for a query. With that, you can create a baseline for the good plan from the execution plans available in SPM and enable that baseline to ensure that the system picks only the good plan from the baseline.<\/p>\n

To take advantage of this feature, you must identify the `sql_id<\/em>` of the query that is performing well on one instance and performing poorly on another instance. You must also capture the good execution plan ID, the `plan_hash_value<\/em>`,of the query on the instance where it works well.<\/p>\n

Copy the SQL base plan from one instance to another<\/strong><\/p>\n

Use the following steps to copy the SQL base plan from the source instance to the target instance:<\/p>\n

    \n
  1. Run the query on the source instance, where the query works well, so that the query exists in the cursor cache.<\/li>\n
  2. On the source instance, load the SQL execution plan for the query from the cursor cache to SPM as a baseline.<\/li>\n
  3. Create a staging table on the source instance. This table is used to migrate the execution plan from the source instance to the target instance.<\/li>\n
  4. Pack the source execution plan, or baseline, in the staging table on the   source instance.<\/li>\n
  5. Transfer the staging table from the source instance to the target instance by using the export\/import utility.<\/li>\n
  6. Unpack the SQL plan on the target instance from the staging table to SPM.<\/li>\n
  7. Verify that baseline created on target instance is fixed and accepted for the query to select it on next run.<\/li>\n
  8. Test the SQL that had a performance issue on the target instance and verify that it picks the transferred baseline.<\/li>\n<\/ol>\n

    Example Execution<\/strong>
    \nExecuting the preceding steps results in output similar to the following examples.<\/p>\n

    Step 1: Run query on the source instance<\/strong><\/p>\n

    Run the SQL on the source instance and identify the `sql_id` and `plan_hash_value<\/em>`.
    \nExamine the cursor cache to get the values.  In this case, they are the
    \nfollowing values:<\/p>\n

    – `sql_id`: 9xva48wpnsmp6
    \n– `plan_hash_value`: 1572948408)<\/em><\/p>\n

    Execute the following query on the source instance:<\/p>\n

        SQL> select distinct plan_hash_value from v$sql where sql_id=’9xva48wpnsmp6′;<\/em><\/p>\n

        PLAN_HASH_VALUE
    \n    —————
    \n    1572948408<\/p>\n

     Step 2: Load the plan to SPM<\/strong><\/p>\n

    Execute the following query to load this good query execution plan from the
    \ncursor cache to SPM as a baseline:<\/p>\n

        SQL> set serveroutput on
    \n    SQL> declare
    \n    2   ret binary_integer;
    \n         l_sql_id varchar2(13);
    \n    3
    \n    4   l_plan_hash_value number;
    \n    5   l_fixed varchar2(3);
    \n    6   l_enabled varchar2(3);
    \n    7   Begin
    \n    8   l_sql_id := ‘&&sql_id’;
    \n    9   l_plan_hash_value := to_number(‘&&plan_hash_value’);
    \n    10   l_fixed := ‘Yes’;
    \n    11   l_enabled := ‘Yes’;
    \n    12   ret := dbms_spm.load_plans_from_cursor_cache(
    \n    13       sql_id=>l_sql_id,
    \n    14       plan_hash_value=>l_plan_hash_value,
    \n    15       fixed=>l_fixed,
    \n    16       enabled=>l_enabled);
    \n    17   end;
    \n    18  \/<\/em><\/p>\n

        Enter value for sql_id: 9xva48wpnsmp6
    \n    old   8:  l_sql_id := ‘&&sql_id’;
    \n    new   8:  l_sql_id := ‘9xva48wpnsmp6’;<\/em><\/p>\n

        Enter value for plan_hash_value: 1572948408
    \n    old   9:  l_plan_hash_value := to_number(plan_hash_value’);
    \n    new   9:  l_plan_hash_value := to_number(‘1572948408’);<\/em><\/p>\n

        PL\/SQL procedure successfully completed.
    \nExecute the following defined queries to verify that you created the SQL baseline on the source instance. Note the following details for later reference.<\/em><\/p>\n

     SQL> select count(*) from dba_sql_plan_baselines ;<\/em><\/p>\n

        COUNT(*)
    \n    ——–
    \n      1<\/em><\/p>\n

        SQL> select SQL_HANDLE, PLAN_NAME from dba_sql_plan_baselines;<\/em><\/p>\n

        SQL_HANDLE                     PLAN_NAME
    \n    —————————— ——————————
    \n    SQL_d344aac395f978a4           SQL_PLAN_d6j5asfazky54868c96c3<\/em><\/p>\n

    #### Step 3: Create a staging table on the source instance<\/em><\/p>\n

    Execute the following query to create a staging table on the source instance:<\/em><\/p>\n

        SQL> sho user
    \n    USER is “SYS”
    \n    SQL> BEGIN
    \n      DBMS_SPM.CREATE_STGTAB_BASELINE(
    \n      table_name      => ‘SPM_STAGETAB’,
    \n      table_owner     => ‘APPS’,
    \n      tablespace_name => ‘SYSAUX’);
    \n    END;<\/em><\/p>\n

        2    3    4    5    6    7
    \n    8  \/<\/em><\/p>\n

        PL\/SQL procedure successfully completed.<\/em>
    \n <\/p>\n

     Step 4: Pack the baseline<\/strong><\/p>\n

    Execute the following query to pack the baseline in the staging table on the
    \nsource instance:<\/p>\n

        SQL> DECLARE
    \n    2      my_plans number;
    \n    3      BEGIN
    \n    4        my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
    \n             table_name => ‘SPM_STAGETAB’,
    \n             enabled => ‘yes’,
    \n    5
    \n    6
    \n    7        table_owner => ‘APPS’,
    \n    8        plan_name => ‘SQL_PLAN_d6j5asfazky54868c96c3’,
    \n    9      sql_handle => ‘SQL_d344aac395f978a4’);
    \n    10   END;
    \n    11  \/<\/em><\/p>\n

        PL\/SQL procedure successfully completed.<\/p>\n

     Step 5: Transfer the staging table from the source to the target instance<\/strong><\/p>\n

    Execute the following command to take an export backup of the staging table on
    \nthe source instance:<\/p>\n

     exp file=SPM_STAGETAB.dmp tables=APPS.SPM_STAGETAB log=SPM_STAGETAB.log compress=n
    \n    Export: Release 11.2.0.4.0 – Production on Sun Jun 3 13:14:50 2018
    \n    Copyright (c) 1982, 2011, Oracle and\/or its affiliates.  All rights reserved.<\/em><\/p>\n

        Username: system\/*******<\/em><\/p>\n

        Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
    \n    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    \n    Export done in US7ASCII character set and AL16UTF16 NCHAR character set<\/em><\/p>\n

        About to export specified tables via Conventional Path …
    \n    Current user changed to APPS
    \n    . . exporting table             SPM_STAGETAB             1 rows exported
    \n    Export terminated successfully without warnings.<\/em><\/p>\n

    Now, execute the following command on the target instance to transfer the export backup of the staging table to the target instance’s host and import the table in the target instance:<\/p>\n

        imp system file=SPM_STAGETAB.dmp log=imp_SPM_STAGETAB.log fromuser=apps touser=apps<\/em><\/p>\n

        Import: Release 11.2.0.4.0 – Production on Sun Jun 3 14:16:25 2018<\/em><\/p>\n

        Copyright (c) 1982, 2011, Oracle and\/or its affiliates.  All rights reserved.<\/em><\/p>\n

        Password:<\/em><\/p>\n

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
    \n    With the Partitioning, OLAP, Data Mining and Real Application Testing options<\/p>\n

        Export file created by EXPORT:V11.02.00 via conventional path<\/em>
    \n    import done in US7ASCII character set and AL16UTF16 NCHAR character set<\/em>
    \n    . importing APPS’s objects into APPS
    \n    . . importing table           “SPM_STAGETAB”              1 rows imported
    \n    Import terminated successfully without warnings.<\/p>\n

    Step 6: Unpack the baseline<\/strong>
    \nExecute the following commands to unpack the baseline from staging table to the target instance’s SPM. In the following example, take a count before unpacking the baseline to verify that the baseline was imported properly on the target.
    \n SQL> select count(*) from dba_sql_plan_baselines;<\/em><\/p>\n

     <\/p>\n

      COUNT(*)
    \n    ——–
    \n      2<\/em><\/p>\n

        SQL> SET SERVEROUTPUT ON
    \n    SQL> DECLARE
    \n    2      l_plans_unpacked  PLS_INTEGER;
    \n    3         BEGIN
    \n    4         l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
    \n    5               table_name      => ‘SPM_STAGETAB’,
    \n    6               table_owner     => ‘APPS’);
    \n    7
    \n    8            DBMS_OUTPUT.put_line(‘Plans Unpacked: ‘ || l_plans_unpacked);
    \n    9      END;
    \n    10  \/
    \n    Plans Unpacked: 1<\/em><\/p>\n

        PL\/SQL procedure successfully completed.<\/em>
    \n <\/p>\n

        SQL> select count(*) from dba_sql_plan_baselines;<\/em><\/p>\n

        COUNT(*)
    \n    ——–
    \n      3<\/p>\n

    Step 7: Verify the baseline<\/strong><\/p>\n

    Run the following commands on the target instance to verify that the baseline
    \nis accepted and fixed.<\/p>\n

        SQL> SELECT sql_handle, plan_name, enabled, accepted, fixed, origin FROM dba_sql_plan_baselines;<\/em><\/p>\n

        SQL_HANDLE            PLAN_NAME                      ENA ACC FIX ORIGIN
    \n    ——————— —————————— — — — ————
    \n    SQL_d344aac395f978a4  SQL_PLAN_d6j5asfazky54868c96c3 YES YES NO  MANUAL-LOAD<\/p>\n

        SQL>
    \n <\/p>\n

    The preceding output shows that the baseline was imported on target instance but that it is not fixed. Run the following query to fix the baseline and enable the optimizer to pick only this plan.<\/p>\n

        SQL> DECLARE
    \n    2    l_plans_altered  PLS_INTEGER;
    \n    3  BEGIN
    \n    4    l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    \n    5      sql_handle      => ‘SQL_d344aac395f978a4’,
    \n    6      PLAN_NAME       => ‘SQL_PLAN_d6j5asfazky54868c96c3’,
    \n    7      ATTRIBUTE_NAME  => ‘fixed’,
    \n    8      attribute_value => ‘YES’);<\/em>
    \n    9    DBMS_OUTPUT.put_line(‘Plans Altered: ‘ || l_plans_altered);
    \n    10  END;
    \n    11  \/<\/p>\n

        PL\/SQL procedure successfully completed.<\/p>\n

        SQL> SELECT sql_handle, plan_name, enabled, accepted, fixed, origin FROM   dba_sql_plan_baselines;<\/p>\n

        SQL_HANDLE            PLAN_NAME                      ENA ACC FIX ORIGIN
    \n    ——————— —————————— — — — ————
    \n    SQL_d344aac395f978a4  SQL_PLAN_d6j5asfazky54868c96c3 YES YES YES MANUAL-LOAD<\/p>\n

        SQL><\/p>\n

    Step 8: Test the SQL query on the target instance<\/strong><\/p>\n

    Execute the following command on the target instance to verify that it picks up
    \nthe new baseline:<\/p>\n

        SQL> select SQL_PLAN_BASELINE from v$sql where sql_id=’9xva48wpnsmp6′;<\/p>\n

        SQL_PLAN_BASELINE
    \n    ——————————
    \n    SQL_PLAN_d6j5asfazky54868c96c3<\/p>\n

    How the SQL plan is selected<\/strong>
    \nThe following image shows how a SQL plan is selected when a baseline plan exists:<\/p>\n

    \"sql<\/p>\n

     Image source<\/em>: Metalink Note Automatic SQL Plan Baselines (Doc ID 1930525.1)<\/p>\n

    Conclusion<\/strong>
    \nUse the steps in this post if you have to transfer the baseline for a single query. You can also generate SQL baselines for all the queries for upgrades, migrations, and so on. Use an SQL plan baseline to have consistent SQL execution plans and to avoid any performance issues.<\/p>\n","protected":false},"excerpt":{"rendered":"

    Transfer SQL plans by using a SQL plan baseline<\/p>\n","protected":false},"author":2,"featured_media":7976,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26560","content_type":"blog","created_by":"10130","authored_on":"2024-01-31T16:03:51+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Manoj Chawla, Oracle Applications Database Administrator, Rackspace Technology","seo_description":"Understand the role of SQL plan baselines in transferring execution plans between instances, improving SQL query performance.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26560-insights_image-productivity-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"MySQL, NoSQL, Multicloud Security","product":"","publisheddate":"2019-03-11T15:03:51","rxt_solutions":"Databases, Data Modernization","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Mastering SQL Plan Baselines: Transferring SQL Plans | Rackspace Technology","field_topics":"","field_slug":"transfer-sql-plans-by-using-sql-plan-baseline","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7974","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7974","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7974"}],"version-history":[{"count":3,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7974\/revisions"}],"predecessor-version":[{"id":9796,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7974\/revisions\/9796"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7976"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7974"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7974"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7974"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}