A customer called with a request to transfer a specific SQL plan from production to test.
The query was found in the shared pool:
SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active",
SQL_ID,
PLAN_HASH_VALUE,
SQL_PLAN_BASELINE "BASELINE",
SQL_PROFILE,
IS_RESOLVED_ADAPTIVE_PLAN "AdaptivePlan",
CHILD_NUMBER AS "Child Num",
ELAPSED_TIME,
EXECUTIONS,
ROUND(ELAPSED_TIME/1000000) "duration (sec)"
,CASE WHEN EXECUTIONS > 0 THEN
ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1)
ELSE
NULL
END "sec per exe"
FROM V$SQL
WHERE SQL_ID in('5cbuqgt5m5961')
ORDER BY LAST_ACTIVE_TIME DESC;
| last active |
SQL_ID |
PLAN_HASH_VALUE |
BASELINE |
SQL_PROFILE |
AdaptivePlan |
Child Num |
ELAPSED_TIME |
EXECUTIONS |
duration (sec) |
sec per exe |
| 21.10.2020 13:34 |
5cbuqgt5m5961 |
3389950398 |
SQL_PLAN_ds58srfg86uf4f5a21fb1 |
SYS_SQLPROF_014fb0ca24980001 |
|
1 |
210182320 |
1402169 |
210 |
0 |
In the test database, the same query does not have a plan in the SMB, nor does it have a SQL Profile. The same query as above gave me this result in the test database:
| last active |
SQL_ID |
PLAN_HASH_VALUE |
BASELINE |
SQL_PROFILE |
AdaptivePlan |
Child Num |
ELAPSED_TIME |
EXECUTIONS |
duration (sec) |
sec per exe |
| 21.10.2020 10:47 |
5cbuqgt5m5961 |
1355560190 |
|
|
|
0 |
110508458900 |
27032 |
110508 |
4,1 |
We see that the query needs a SQL Plan baseline and a SQL Profile to perform well.
Oracle has a way to export the metadata needed to stabilize the query plan, and import it in another database.
When exporting SQL Plan baselines you would use the package
dbms_spm
Here is what I did to transfer the SQL Plan from one database to another.
I am connecting to the databases as a privileged user called dbadmin throughout this post.
In part two of this post, I will show you how I transferred the SQL Profile from one database to another.
1. On the source database, create a staging table for SQL Plan Baselines:
vi 1.cre_stagetab.sql
Add the following:
connect dbadmin
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE (
table_name => 'MYSTAGETAB');
END;
/
Execute the file:
sqlplus /nolog @1.cre_stagetab.sql
Still in the source database, check who the owner of the baseline is:
SELECT plan_name,
sql_handle,
creator,
origin,
TO_CHAR (created, 'dd.mm.yyyy hh24:mi') "created",
TO_CHAR (last_executed, 'dd.mm.yyyy hh24:mi') "lastexecuted",
enabled,
accepted,
fixed,
autopurge,
module,
ROUND (ELAPSED_TIME / 1000000) "duration in seconds",
cpu_time,
buffer_gets,
disk_reads
FROM DBA_SQL_PLAN_BASELINES
where plan_name='SQL_PLAN_ds58srfg86uf4f5a21fb1';
The output shows that the owner is SYS:
| PLAN_NAME |
SQL_HANDLE |
CREATOR |
ORIGIN |
created |
lastexecuted |
ENABLED |
ACCEPTED |
FIXED |
AUTOPURGE |
MODULE |
duration in seconds |
CPU_TIME |
BUFFER_GETS |
DISK_READS |
| SQL_PLAN_ds58srfg86uf4f5a21fb1 |
SQL_dc1518bb9e8369c4 |
SYS |
MANUAL-LOAD-FROM-STS |
30.03.2020 15:13 |
18.10.2020 03:13 |
YES |
YES |
YES |
YES |
JDBC Thin Client |
8 |
3897100 |
597270 |
715 |
2. Create the file 2.pack_stagetab.sql:
vi 2.pack_stagetab.sql
Add the following:
conn dbadmin
alter session set nls_language='american';
DECLARE
v_plan_cnt NUMBER;
BEGIN
v_plan_cnt := DBMS_SPM.PACK_STGTAB_BASELINE (
table_name => 'MYSTAGETAB'
, enabled => 'yes'
, creator => 'SYS'
, plan_name => 'SQL_PLAN_ds58srfg86uf4f5a21fb1'
);
END;
/
exit
Run the file:
sqlplus /nolog @2.pack_stagetab.sql
Let's verify that the staging table has some rows in it:
connect dbadmin
select count(*)
from MYSTAGETAB;
3. Create an export parameter file:
vi 3.expdp.par
Add the following. Make sure the directory DP exist and is writable:
userid=dbadmin
tables=MYSTAGETAB
logfile=expdp_MYSTAGETAB.log
dumpfile=MYSTAGETAB.dmp
job_name=exp_MYSTAGETAB
directory=DP
Run the export:
expdp parfile=3.expdp.par
When the export is done, transfer the file to the destination server using ssh.
At this point logon to the destination server.
vi 4.impdp.par
Add the following. Make sure the directory DP exist and is writable:
userid=dbadmin
tables=MYSTAGETAB
logfile=impdp_MYSTAGETAB.log
dumpfile=MYSTAGETAB.dmp
job_name=imp_MYSTAGETAB
directory=DP
Run the import:
impdp parfile=4.impdp.par
5. Create the file for unpacking the SQL Plans to the local SMB:
vi 5.unpack.sql
Add the following:
conn dbadmin
alter session set nls_language='american';
set serveroutput on
DECLARE
v_plan_cnt NUMBER;
BEGIN
v_plan_cnt := DBMS_SPM.UNPACK_STGTAB_BASELINE (
table_name => 'MYSTAGETAB'
,plan_name => 'SQL_PLAN_ds58srfg86uf4f5a21fb1'
);
DBMS_OUTPUT.put_line('Number of plans loaded: ' || v_plan_cnt);
END;
/
exit
Run the file:
sqlplus /nolog @5.unpack.sql
You should now be able to see a different execution plan when executing the query at the top of this article:
| last active |
SQL_ID |
PLAN_HASH_VALUE |
BASELINE |
SQL_PROFILE |
AdaptivePlan |
Child Num |
ELAPSED_TIME |
EXECUTIONS |
duration (sec) |
sec per exe |
| 21.10.2020 13:19 |
5cbuqgt5m5961 |
1355560190 |
|
|
|
1 |
20850937263 |
1488 |
20851 |
14 |
| 21.10.2020 12:55 |
5cbuqgt5m5961 |
1355560190 |
|
|
|
0 |
112190741129 |
27134 |
112191 |
4,1 |
The performance actually got worse!
The reason is that we are missing the SQL Profile
I will now transfer the SQL Profile to the same server in the
next post.
