Search

Top 60 Oracle Blogs

Recent comments

Copying a SQL Plan Baseline from one database to another

Hopefully this post saves you a few minutes looking the procedure up. I know it’ll save me some time ;) In this rather lengthy article I’d like to cover how I copied a SQL Plan Baseline from one database to another. If you find this procedure useful, please ensure your system is appropriately licensed for it and test it first!

My Setup

My source database is named ORA19NCDB, patched to 19.7.0 running on Oracle Linux 7x/UEK 5. As I do so often, I’m using Dominic Giles’s Swingbench as the source for this experiment. This is the query in question:

SELECT oi.order_id,
    SUM(oi.unit_price * oi.quantity)
FROM
    soe.orders         o,
    soe.order_items    oi
WHERE
        o.order_id = oi.order_id
    and o.order_date = DATE '2012-04-01'
    and o.delivery_type = 'Collection'
GROUP BY
    oi.order_id
ORDER BY
    2 DESC; 

I would like to make sure the execution plan for this statement is available to my destination database. Like the source, it’s a 19.7.0 database running on Linux.

Create the SQL Plan Baseline

In the first step I need to create the SQL Plan Baseline, for which I need the SQL ID and plan hash value. One way to get these is to run the query and check the cursor cache:

SQL> @/tmp/query

  ORDER_ID SUM(OI.UNIT_PRICE*OI.QUANTITY)
---------- ------------------------------
   1138103                          30648

...

    244488                           3696

32 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  cbynz8srbydr7, child number 0
-------------------------------------
SELECT oi.order_id,     SUM(oi.unit_price * oi.quantity) FROM
soe.orders         o,     soe.order_items    oi WHERE
o.order_id = oi.order_id     and o.order_date = DATE '2012-04-01'
and o.delivery_type = 'Collection' GROUP BY     oi.order_id ORDER BY
 2 DESC

Plan hash value: 1126116122

---------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name              | E-Rows |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                   |        |       |       |          |
|   1 |  SORT ORDER BY                          |                   |      1 |  2048 |  2048 | 2048  (0)|
|   2 |   HASH GROUP BY                         |                   |      1 |  1160K|  1160K| 1429K (0)|
|   3 |    NESTED LOOPS                         |                   |      1 |       |       |          |
|   4 |     NESTED LOOPS                        |                   |      3 |       |       |          |
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS            |      1 |       |       |          |
|*  6 |       INDEX RANGE SCAN                  | ORD_ORDER_DATE_IX |      1 |       |       |          |
|*  7 |      INDEX RANGE SCAN                   | ITEM_ORDER_IX     |      3 |       |       |          |
|   8 |     TABLE ACCESS BY INDEX ROWID         | ORDER_ITEMS       |      3 |       |       |          |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("O"."DELIVERY_TYPE"='Collection')
   6 - access("O"."ORDER_DATE"=TO_DATE(' 2012-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("O"."ORDER_ID"="OI"."ORDER_ID")

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


37 rows selected. 

With the SQL ID and PHV identified I can create a SQL Plan Baseline for this query. I have a little script to do that for me:

SQL> get create_baseline
  1  pro Create a SQL Plan Baseline based on SQL ID and Plan Hash Value
  2  var count number
  3  exec :count := dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sqlid',plan_hash_value=>&phv)
  4* select :count || ' SQL Plan Baselines captured' as result from dual;

SQL> start create_baseline
Create a SQL Plan Baseline based on SQL ID and Plan Hash Value
Enter value for sqlid: cbynz8srbydr7
Enter value for phv: 1126116122

PL/SQL procedure successfully completed.


RESULT
--------------------------------------------------------------------
1 SQL Plan Baselines captured

SQL>  

The baseline should have been captured, as per the “result” column. It’s simple enough to verify.

SQL> select sql_handle, plan_name, origin, enabled, accepted, fixed 
  2  from dba_sql_plan_baselines where created > systimestamp - interval '5' minute;

SQL_HANDLE                     PLAN_NAME                      ORIGIN                         ENA ACC FIX
------------------------------ ------------------------------ ------------------------------ --- --- ---
SQL_6f18f4fc0f67407e           SQL_PLAN_6y67nzh7qfh3ya116ef60 MANUAL-LOAD-FROM-CURSOR-CACHE  YES YES NO

A quick check should be enough to show the baseline is used:

SQL> @/tmp/query

  ORDER_ID SUM(OI.UNIT_PRICE*OI.QUANTITY)
---------- ------------------------------
   1138103                          30648

...

    244488                           3696

32 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID  cbynz8srbydr7, child number 0
-------------------------------------
SELECT oi.order_id,     SUM(oi.unit_price * oi.quantity) FROM
soe.orders         o,     soe.order_items    oi WHERE
o.order_id = oi.order_id     and o.order_date = DATE '2012-04-01'
and o.delivery_type = 'Collection' GROUP BY     oi.order_id ORDER BY
 2 DESC

Plan hash value: 1126116122

---------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name              | E-Rows |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                   |        |       |       |          |
|   1 |  SORT ORDER BY                          |                   |      1 |  2048 |  2048 | 2048  (0)|
|   2 |   HASH GROUP BY                         |                   |      1 |  1160K|  1160K| 1425K (0)|
|   3 |    NESTED LOOPS                         |                   |      1 |       |       |          |
|   4 |     NESTED LOOPS                        |                   |      3 |       |       |          |
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS            |      1 |       |       |          |
|*  6 |       INDEX RANGE SCAN                  | ORD_ORDER_DATE_IX |      1 |       |       |          |
|*  7 |      INDEX RANGE SCAN                   | ITEM_ORDER_IX     |      3 |       |       |          |
|   8 |     TABLE ACCESS BY INDEX ROWID         | ORDER_ITEMS       |      3 |       |       |          |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("O"."DELIVERY_TYPE"='Collection')
   6 - access("O"."ORDER_DATE"=TO_DATE(' 2012-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("O"."ORDER_ID"="OI"."ORDER_ID")

Note
-----
   - SQL plan baseline SQL_PLAN_6y67nzh7qfh3ya116ef60 used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


38 rows selected. 

This looks good! I can see the SQL Plan Baseline used as per the “Note” section.

Transferring the SQL Plan Baseline

Now the fun begins. I need to transfer the SQL Plan Baseline from my source system to the target. The procedure is well documented, and I really like the 11.2 documentation. There is of course more out there, but this is the most concise piece of information in my opinion.

To copy the SQL Plan Baseline I need to first put it into a staging table and then export it from the source to import it into the destination database. I have chosen to store the baseline in the SOE schema, but that’s of course up to you. This little script can help save some time:

set serveroutput on
prompt prepare a SQL Plan Baseline for transport
prompt
accept v_table_name  prompt 'enter the name of the staging table to be created: ' 
accept v_table_owner prompt 'enter the schema name where the staging table is to be created: ' 
accept v_sql_handle  prompt 'which SQL handle should be exported? ' 
accept v_plan_name prompt 'enter the corresponding plan name: ' 

declare
  v_packed_baselines number;
begin
  dbms_spm.create_stgtab_baseline( 
    table_name => '&v_table_name', 
    table_owner => '&v_table_owner');

  v_packed_baselines := dbms_spm.pack_stgtab_baseline(
    table_name => '&v_table_name',
    table_owner => '&v_table_owner',
    sql_handle => '&v_sql_handle',
    plan_name => '&v_plan_name');
  dbms_output.put_line(v_packed_baselines || ' baselines have been staged in &v_table_owner..&v_table_name');
end;
/ 

set serveroutput off

This procedure should confirm a single SQL Plan Baseline to have been staged:

SQL> start prep_transport
prepare a SQL Plan Baseline for transport

enter the name of the staging table to be created: blogpost
enter the schema name where the staging table is to be created: soe
which SQL handle should be exported? SQL_6f18f4fc0f67407e
enter the corresponding plan name: SQL_PLAN_6y67nzh7qfh3ya116ef60
1 baselines have been staged in soe.blogpost

PL/SQL procedure successfully completed. 

Next, export the table and import it into the destination database. Here is the output from my system:

$ expdp directory=the_export_dir tables=soe.blogpost dumpfile=soe_blogpost.dmp logfile=soe_blogpost.log

Export: Release 19.0.0.0.0 - Production on Tue Jun 2 17:54:13 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: martin  
Password: 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "MARTIN"."SYS_EXPORT_TABLE_01":  martin/******** directory=the_export_dir tables=soe.blogpost dumpfile=soe_blogpost.dmp logfile=soe_blogpost.log 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SOE"."BLOGPOST"                            51.74 KB       9 rows
Master table "MARTIN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MARTIN.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/ORA19NCDB/dpdump/soe_blogpost.dmp
Job "MARTIN"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jun 2 17:54:39 2020 elapsed 0 00:00:22 

The import is equally simple:

$ impdp directory=the_import_dir logfile=soe_blogpost.log dumpfile=soe_blogpost.dmp

Import: Release 19.0.0.0.0 - Production on Tue Jun 2 16:59:39 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: martin
Password: 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "MARTIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "MARTIN"."SYS_IMPORT_FULL_01":  martin/******** directory=the_import_dir logfile=soe_blogpost.log dumpfile=soe_blogpost.dmp 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SOE"."BLOGPOST"                            51.74 KB       9 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "MARTIN"."SYS_IMPORT_FULL_01" successfully completed at Tue Jun 2 17:00:22 2020 elapsed 0 00:00:26 

Making the SQL Plan Baseline available and using it

After the import completed successfully it’s time to unpack the SQL Plan Directive. The following code snippet did that for me

var num_unpacked number

begin
  :num_unpacked := dbms_spm.unpack_stgtab_baseline(
    table_name => '&table_name',
    table_owner => '&table_owner');
end;
/

print :num_unpacked 

After which you can see it in the database:

SQL> select sql_handle, plan_name, origin, enabled, accepted, fixed
  2   from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      ORIGIN                        ENA ACC FIX
------------------------------ ------------------------------ ----------------------------- --- --- ---
SQL_6f18f4fc0f67407e           SQL_PLAN_6y67nzh7qfh3ya116ef60 MANUAL-LOAD-FROM-CURSOR-CACHE YES YES NO

SQL>  

Any execution of SQL ID cbynz8srbydr7 will now use the imported SQL Plan Baseline.

SQL> l
  1  SELECT oi.order_id,
  2     SUM(oi.unit_price * oi.quantity)
  3  FROM
  4     soe.orders     o,
  5     soe.order_items    oi
  6  WHERE
  7         o.order_id = oi.order_id
  8     and o.order_date = DATE '2012-04-01'
  9     and o.delivery_type = 'Collection'
 10  GROUP BY
 11     oi.order_id
 12  ORDER BY
 13*     2 DESC
SQL> /

[...]

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  cbynz8srbydr7, child number 0

SELECT oi.order_id,     SUM(oi.unit_price * oi.quantity) FROM
soe.orders         o,     soe.order_items    oi WHERE
o.order_id = oi.order_id     and o.order_date = DATE '2012-04-01'
and o.delivery_type = 'Collection' GROUP BY     oi.order_id ORDER BY
 2 DESC

Plan hash value: 1126116122

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                   |       |       |     3 (100)|          |
|   1 |  SORT ORDER BY                          |                   |     1 |    74 |     3  (67)| 00:00:01 |
|   2 |   HASH GROUP BY                         |                   |     1 |    74 |     3  (67)| 00:00:01 |
|   3 |    NESTED LOOPS                         |                   |     1 |    74 |     1   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                        |                   |     1 |    74 |     1   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS            |     1 |    35 |     1   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN                  | ORD_ORDER_DATE_IX |     1 |       |     1   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN                   | ITEM_ORDER_IX     |     1 |       |     0   (0)|          |
|   8 |     TABLE ACCESS BY INDEX ROWID         | ORDER_ITEMS       |     1 |    39 |     0   (0)|          |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("O"."DELIVERY_TYPE"='Collection')
   6 - access("O"."ORDER_DATE"=TO_DATE(' 2012-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("O"."ORDER_ID"="OI"."ORDER_ID")

Note
-----
   - SQL plan baseline SQL_PLAN_6y67nzh7qfh3ya116ef60 used for this statement


35 rows selected.

This fact is also replicated in v$sql:

SQL> select sql_id, child_number, plan_hash_value, sql_plan_baseline, executions from v$sql where sql_id = 'cbynz8srbydr7';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS
------------- ------------ --------------- ------------------------------ ----------
cbynz8srbydr7            0      1126116122 SQL_PLAN_6y67nzh7qfh3ya116ef60          2 

Summary

It isn’t too hard to transfer a SQL Plan Baseline from one host to another, as this post hopes to demonstrate.