This uses the coe_xfr_sql_profile.sql which is part of sqlt download from Oracle, but also available from other sites such as :
http://kerryosborne.oracle-guy.com/scripts/coe_xfr_sql_profile.sqlonce downloaded from SQLT or above we can use it to pin a SQL query to a good plan as below:
@coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: cqx5uak6fvf06
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
4122059633 .02
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 4122059633
Values passed:
~~~~~~~~~~~~~
SQL_ID : "cqx5uak6fvf06"
PLAN_HASH_VALUE: "4122059633"
Execute coe_xfr_sql_profile_cqx5uak6fvf06_4122059633.sql
on TARGET system in order to create a custom SQL Profile
with plan 4122059633 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
This creates a file with the SQLID and plan hash in the name, if we view the file
we see some notes on how it should be run:
REM NOTES
REM 1. Should be run as SYSTEM or SYSDBA.
REM 2. User must have CREATE ANY SQL PROFILE privilege.
REM 3. SOURCE and TARGET systems can be the same or similar.
REM 4. To drop this custom SQL Profile after it has been created:
REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(‘coe_cqx5uak6fvf06_4122059633’);
REM 5. Be aware that using DBMS_SQLTUNE requires a license
REM for the Oracle Tuning Pack.
Also note the script allows is to force match the SQL plan pin so if SQL is using literals which change it will still be used (default is False)
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
To implement the profile we simply run the script into the database
sqlplus / as sysdba
@coe_xfr_sql_profile_cqx5uak6fvf06_4122059633.sqlNote my script I have SQLID of cqx5uak6fvf06 using plan hash of 4122059633
SQL> @coe_xfr_sql_profile_cqx5uak6fvf06_4122059633.sql
SQL> REM
SQL> REM $Header: 215187.1 coe_xfr_sql_profile_cqx5uak6fvf06_4122059633.sql 11.4.1.4 2023/12/29 csierra $
SQL> REM
SQL> REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved.
SQL> REM
SQL> REM AUTHOR
SQL> REM carlos.sierra@oracle.com
SQL> REM
SQL> REM SCRIPT
SQL> REM coe_xfr_sql_profile_cqx5uak6fvf06_4122059633.sql
SQL> REM
SQL> REM DESCRIPTION
SQL> REM This script is generated by coe_xfr_sql_profile.sql
SQL> REM It contains the SQL*Plus commands to create a custom
SQL> REM SQL Profile for SQL_ID cqx5uak6fvf06 based on plan hash
SQL> REM value 4122059633.
SQL> REM The custom SQL Profile to be created by this script
SQL> REM will affect plans for SQL commands with signature
SQL> REM matching the one for SQL Text below.
SQL> REM Review SQL Text and adjust accordingly.
SQL> REM
SQL> REM PARAMETERS
SQL> REM None.
SQL> REM
SQL> REM EXAMPLE
SQL> REM SQL> START coe_xfr_sql_profile_cqx5uak6fvf06_4122059633.sql;
SQL> REM
SQL> REM NOTES
SQL> REM 1. Should be run as SYSTEM or SYSDBA.
SQL> REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL> REM 3. SOURCE and TARGET systems can be the same or similar.
SQL> REM 4. To drop this custom SQL Profile after it has been created:
SQL> REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_cqx5uak6fvf06_4122059633');
SQL> REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL> REM for the Oracle Tuning Pack.
SQL> REM
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL> REM
SQL> VAR signature NUMBER;
SQL> REM
SQL> DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 BEGIN
5 sql_txt := q'[
6 select /*+ full(test1) */ * from test1 where object_id=2
7 ]';
8 h := SYS.SQLPROF_ATTR(
9 q'[BEGIN_OUTLINE_DATA]',
10 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
11 q'[OPTIMIZER_FEATURES_ENABLE('12.1.0.2')]',
12 q'[DB_VERSION('12.1.0.2')]',
13 q'[ALL_ROWS]',
14 q'[OUTLINE_LEAF(@"SEL$1")]',
15 q'[FULL(@"SEL$1" "TEST1"@"SEL$1")]',
16 q'[END_OUTLINE_DATA]');
17 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
18 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
19 sql_text => sql_txt,
20 profile => h,
21 name => 'coe_cqx5uak6fvf06_4122059633',
22 description => 'coe cqx5uak6fvf06 4122059633 '||:signature||'',
23 category => 'DEFAULT',
24 validate => TRUE,
25 replace => TRUE,
26 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
27 END;
28 /
PL/SQL procedure successfully completed.
SQL> WHENEVER SQLERROR CONTINUE
SQL> SET ECHO OFF;
SIGNATURE
---------------------
4615232642055531137
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_cqx5uak6fvf06_4122059633 completed
select name, sql_text, status, force_matching from DBA_SQL_PROFILES;
Check the profile exists in the database
May need to purge sqlplan from cache using:
select sql_text||chr(10)|| 'exec dbms_shared_pool.purge ('||''''||address||','||hash_value||''''||','||''''||'C'||''''||');'
from v$sqlarea
where sql_id='cqx5uak6fvf06'
/
If we now run SQL with autotrace on we see:
SQL profile “coe_cqx5uak6fvf06_4122059633” used for this statement
select sql_id, PLAN_HASH_VALUE, SQL_PROFILE from v$sql where sql_id='cqx5uak6fvf06';
SQL_ID PLAN_HASH_VALUE SQL_PROFILE
------------- --------------- ----------------------------------------------------------------
cqx5uak6fvf06 4122059633 coe_cqx5uak6fvf06_4122059633
SQL Plan profile can also be dropped as above using:
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_cqx5uak6fvf06_4122059633');