Table of Contents
Introduction
In this article, we'll walk through the steps on How to copy plan from one database to another in Oracle 19c.
I came up with an interesting topic that is very useful when you are dealing with performance tuning in Oracle. One of the application team members raised a ticket to our queue and said our SQL query was running very slow in one of their databases. However, the same query is working fine in another environment.
So I have checked several things that we usually check in long-running queries for which I have already created an article where I have explained detailed steps on how to tackle long-running sessions in Oracle. You can check the same.
Let us come to our topic where I collected the details from the application team where the query is running fine and decided to copy plan from one database to another in Oracle 19c.
Copy plan from one database to another in Oracle 19c
Source Side Operations
==================
1. Run a query on the source instance where the plan is good
sys@ORA0XXXX> select distinct plan_hash_value from v$sql where sql_id='0fdbqgg4a8hup';
PLAN_HASH_VALUE
——————————–
3491936407
2. Now we need to load the plan to SPM (SQL Plan Management)
sys@ORA0XXXX> set serveroutput on
declare
ret binary_integer;
l_sql_id varchar2(13);
l_plan_hash_value number;
l_fixed varchar2(3);
l_enabled varchar2(3);
Begin
l_sql_id := '&&sql_id';
l_plan_hash_value := to_number('&&plan_hash_value');
l_fixed := 'Yes';
l_enabled := 'Yes';
ret := dbms_spm.load_plans_from_cursor_cache(
sql_id=>l_sql_id,
plan_hash_value=>l_plan_hash_value,
fixed=>l_fixed,
enabled=>l_enabled);
end;
/
sys@ORA0XXXX> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
old 8: l_sql_id := '&&sql_id';
new 8: l_sql_id := '0fdbqgg4a8hup';
old 9: l_plan_hash_value := to_number('&&plan_hash_value');
new 9: l_plan_hash_value := to_number('3491936407');
PL/SQL procedure successfully completed.
Check the count and PLAN_NAME from the view called dba_sql_plan_baselines.
sys@ORA0XXXX> select count() from dba_sql_plan_baselines ;
COUNT(*)
—————-
1
sys@ORA0XXXX> select SQL_HANDLE, PLAN_NAME from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
——————– ———————
SQL_59af0a6ce8bc9529 SQL_PLAN_5mbsadmnbt599cd8cb624
1 row selected.
3. Create a staging table on the source instance
sys@ORA0XXXX> show user
USER is "SYS"
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'SPM_STAGETAB',
table_owner => 'SYSADM',
tablespace_name => 'SYSAUX');
END;
/
USER is "SYS"
PL/SQL procedure successfully completed.
4. Pack the baseline.
sys@ORA0XXXX> DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'SPM_STAGETAB',
enabled => 'yes',
table_owner => 'SYSADM',
plan_name => 'SQL_PLAN_5mbsadmnbt599cd8cb624',
sql_handle => 'SQL_59af0a6ce8bc9529');
END;
/
PL/SQL procedure successfully completed.
5. Transfer the staging table from the source to the target instance
ORA0XXXX@test:/home/oracle> exp file=SPM_STAGETAB.dmp tables=SYSADM.SPM_STAGETAB log=SPM_STAGETAB.log compress=n
Export: Release 19.0.0.0.0 – Production on Thu Nov 28 02:45:09 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.16.0.0.0
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
Current user changed to SYSADM
. . exporting table SPM_STAGETAB 24 rows exported
Export terminated successfully without warnings.
ORA0XXXX@test:/home/oracle> ls -lrt
-rw-r–r– 1 oracle dba 394 Nov 28 02:45 SPM_STAGETAB.log
-rw-r–r– 1 oracle dba 106496 Nov 28 02:45 SPM_STAGETAB.dmp
Change the permission of the dump file.
==============================
ORA0XXXX@test:/home/oracle> chmod 777 SPM_STAGETAB.dmp
Transfer this dump file to the server where this plan needs to be unpacked.
====================================================
ORA0XXXX@test:/home/oracle> scp SPM_STAGETAB.dmp test2:/tmp
SPM_STAGETAB.dmp 100% 104KB 78.2MB/s 00:00
Target Side Operations
==================
6. Import the table in the target instance
ORAXXXX@test2:/tmp> imp file=SPM_STAGETAB.dmp log=imp_SPM_STAGETAB.log fromuser=SYSADM touser=SYSADM
Import: Release 19.0.0.0.0 – Production on Thu Nov 28 02:47:15 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.16.0.0.0
Export file created by EXPORT:V19.00.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SYSADM's objects into SYSADM
. . importing table "SPM_STAGETAB" 24 rows imported
Import terminated successfully with warnings.
7. Unpack the baseline
Run the commands below to transfer the baseline from the staging table to the target instance's SPM. In the given example, verify the count beforehand to ensure the baseline has been successfully imported into the target.
a) sys@ORAXXXX> select count() from dba_sql_plan_baselines ;
COUNT(*)
—————-
0
b) sys@ORAXXXX> SET SERVEROUTPUT ON
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'SPM_STAGETAB',
table_owner => 'SYSADM');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/
Plans Unpacked: 1
PL/SQL procedure successfully completed.
c) sys@ORAXXXX> select count() from dba_sql_plan_baselines ;
COUNT(*)
—————-
1
d) col PLAN_NAMe for a30
select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
If the issue is not resolved automatically, follow the steps below. The previous output indicates that the baseline was imported into the target instance but remains unresolved. Execute the following query to correct the baseline and ensure the optimizer selects only this plan.
a) sys@ORAXXXX> DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SQL_59af0a6ce8bc9529',
PLAN_NAME => 'SQL_PLAN_5mbsadmnbt599cd8cb624',
ATTRIBUTE_NAME => 'fixed',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
Plans Altered: 1
PL/SQL procedure successfully completed
b) col PLAN_NAME for a30
col origin for a30
select sql_handle, plan_name, enabled, accepted, fixed, origin from dba_sql_plan_baselines;
8. Ask the application team to re-run the query and check if a good plan has been taken
sys@ORAXXXX> select distinct plan_hash_value from v$sql where sql_id='dp31w8z4k4h17';
PLAN_HASH_VALUE
——————————
3491936407
1 row selected.
Finally!! The application team ran the query again and this time it took a good plan and the issue has been resolved.
Conclusion
We have seen from the above steps how we can fix the long-running query by copy plan from one database to another in Oracle 19c.
If you enjoyed the article, please leave a comment and share it with your friends. Also, let me know which Oracle and MySQL topics you'd like to see covered in future articles.
Note: If you want to set the Lab in your home for practice to get your hands dirty then you can download and install Oracle VirtualBox, followed by the operating system, the Oracle binary software, and finally, create the database.
