For SQL Profiles you depend on the package dbms_sqltune
I am connecting to the databases as a privileged user called dbadmin throughout this post.
1. First, find the name of SQL profile you are about to transfer:
select * from dba_sql_profiles order by created;The query above will return the SQL Profiles in the order in which they were created. Notice the name of the relevant one, as this will be used as an argument in the procedures later.
2. On the source database, create a staging table for SQL Profiles:
vi 2.cre_SQLProfile_stgtab.sqlAdd the following:
connect dbadmin
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
table_name => 'MYSTAGETAB_SQLPROF'
, schema_name => 'DBADMIN'
);
END;
/
exit
Execute the file:
sqlplus /nolog @2.cre_SQLProfile_stgtab.sql3. Pack your SQL Profile into your staging table
Create the file 3.pack_SQLProfile_stgtab.sql:
Make sure the directory DP exists in the database. If not, create it with
vi 3.pack_SQLProfile_stgtab.sqlAdd the following:
conn dbadmin
alter session set nls_language='american';
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
profile_name => 'SYS_SQLPROF_014fb0ca24980001'
, staging_table_name => 'MYSTAGETAB_SQLPROF'
, staging_schema_owner => 'DBADMIN'
);
END;
/
exit
Run the file:
sqlplus /nolog @3.pack_SQLProfile_stgtab.sqlLet's verify that the staging table now has some rows in it:
connect dbadmin select count(*) from MYSTAGETAB_SQLPROF;
4. Create the export parameter file and export the table:
vi 4.expdp_SQLPROFILE_stgtab.sql
Add the following:
userid=dbaadmin
tables=MYSTAGETAB_SQLPROF
logfile=expdp_MYSTAGETAB_SQLPROF.log
dumpfile=MYSTAGETAB_SQLPROF.dmp
job_name=exp_MYSTAGETAB_SQLPROF
directory=DP
create directory DP as '/exports/';Then export the table:
expdp parfile=4.expdp_SQLPROFILE_stgtab.sqlLogon to the destination server.
5. Import the staging table in the destination database
Create an import parameter file:
vi 5.impdp_SQLProfile.parAdd the following:
userid=dbadmin tables=MYSTAGETAB_SQLPROF logfile=impdp_MYSTAGETAB_SQLPROF.log dumpfile=MYSTAGETAB_SQLPROF.dmp job_name=imp_MYSTAGETAB_SQLPROF directory=DPRun the import:
impdp parfile=5.impdp_SQLProfile.par6. Unpack the information in the staging table
Create the file for unpacking the SQL Plans to the local SMB:
Let's verify that the optimizer has actually created a new execution plan based on the SQL Profile:
After the SQL Profile was created, the performance was finally stabelized.
The documentation for the dbms_sqltune package can be found here
vi 6.unpack.sqlAdd the following:
conn dbadmin
alter session set nls_language='american';
set serveroutput on
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
replace => true
, staging_table_name => 'MYSTAGETAB_SQLPROF'
, profile_name => 'SYS_SQLPROF_014fb0ca24980001'
);
END;
/
exit
Execute the file:
sqlplus /nolog @6.unpack.sql
Let's verify that the optimizer has actually created a new execution plan based on the SQL Profile:
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,
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;
Output:
| 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 |
|||
| 21.10.2020 14:47 | 5cbuqgt5m5961 | 3389950398 |
SQL_PLAN_ds58srfg86uf4f5a21fb1 | SYS_SQLPROF_014fb0ca24980001 | 3 |
116651 |
302 |
0 |
0 |
The documentation for the dbms_sqltune package can be found here
