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.
Martins Blog 