OracleAppsDBAKK1

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

GOAL

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

The purpose of this document is to assist in moving an SQL Profile implemented on one database to another database.

NOTE: A SQL profile can be transported to any Oracle database created in the same release or later. The reverse may not be true as profiles are not guaranteed to be backwardly compatible.

See:

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
E16638-07
Chapter 17 Automatic SQL Tuning
Section 17.5.5 Transporting a SQL Profile
http://docs.oracle.com/cd/E36909_01/server.1111/e16638/sql_tune.htm#CHDGHGCJ

SOLUTION

What is a SQL Profile?

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans..

Managing SQL Profiles

For information on SQL Profiles see:

Document 271196.1 Automatic SQL Tuning - SQL Profiles

Steps to Create and Transfer Profile from One Database to Another

The following example illustrates the process of moving a SQL Profile from 10.2 onwards.

1. Create SQL Profile in SCOTT schema

The SQL Profile is created based on the tuning task created and the recommendations given by the tuning task:

DECLARE 
my_task_name VARCHAR2(30);
my_sqltext CLOB; 
my_sqlprofile_name VARCHAR2(30); 

BEGIN 
  my_sqltext := 'select /*+ no_index(emp pk_emp) */ * from emp where empno=7839'; 
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext, 
        user_name => 'SCOTT', 
        scope => 'COMPREHENSIVE', 
        time_limit => 60, 
        task_name => 'my_sql_tuning_task', 
        description => 'Demo Task to tune a query'); 
  
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task'); 

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'my_sql_tuning_task', 
        name => 'my_sql_profile'); 
END; 
/

PL/SQL procedure successfully completed.

set lines 130
set autotrace on

select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 4066871323

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7839)

Note
-----
   - SQL profile "my_sql_profile" used for this statement

NOTE: Even though no_index hint included, the plan uses an index as determined by the SQL profile. The Note section provides plan information that indicates that "my_sql_profile" is used.

2. Creating a staging table to store the SQL Profiles

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT');

PL/SQL procedure successfully completed.

table_name => name of the table to store the SQL Profiles.
schema_name => name of the schema where the table is to be created.

3. Pack the SQL Profiles into the Staging Table

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile');

PL/SQL procedure successfully completed.

staging_table_name => name of the table to store the SQL Profiles.
profile_name => name of the SQL Profile to be packed.

Note: The table_name and schema_name are case-sensitive.

SQL> desc STAGE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROFILE_NAME                                       VARCHAR2(30)
 CATEGORY                                           VARCHAR2(30)
 SIGNATURE                                          NUMBER
 SQL_TEXT                                           CLOB
 DESCRIPTION                                        VARCHAR2(500)
 TYPE                                               VARCHAR2(9)
 STATUS                                             VARCHAR2(8)
 BOOLEAN_FLAGS                                      NUMBER
 ATTRIBUTES                                         SQLPROF_ATTR
 VERSION                                            NUMBER
 SPARE1                                             CLOB
 SPARE2                                             BLOB

4. Export the Staging Table to the Target Database

Using Datapump or Export/Import transfer the table to the target database where you would like to create the same profile.

4a. Export from Source Database

my_linux_1:~> exp scott/<PASSWORD> tables=STAGE

Export: Release 10.2.0.4.0 - Production on Sun Feb 12 17:43:21 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                          STAGE          1 rows exported
Export terminated successfully without warnings.

4b. Import into Target Database

my_linux_1:~> imp scott/<PASSWORD> tables=STAGE

Import: Release 11.2.0.3.0 - Production on Mon Feb 13 14:49:12 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                        "STAGE"          1 rows imported
Import terminated successfully with warnings.

5. Unpack the SQL Profiles

5a. Test before unpacking

SQL> set lines 130
SQL> set autotrace on
SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=7839)

Note: The NO_INDEX hint has been honoured and a FULL table scan has been done on EMP

5b. Unpack Staging Table

If importing to the same schema, schema owner does not need to be specified:
SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE');

However, if importing to different schema, the staging schema owner needs to be changed:|
SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE',staging_schema_owner => 'SQLTXPLAIN');

PL/SQL procedure successfully completed.

6. Check the SQL Profile is enabled in Target Database

set lines 130
set autotrace on

select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 4066871323

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7839)

Note
-----
   - SQL profile "my_sql_profile" used for this statement

Encouraging CBO to Pickup a Better Execution Plan Using the COE XFR SQL Profile Script (Doc ID 1955195.1) | SQL TUNING

PPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.2 and later
Information in this document applies to any platform.

GOAL

This Document helps in situations where a plan may have changed for some reason, but a good plan is still available and reproducible.

SOLUTION

Under certain circumstances the plan for an sql may unexpectedly change to a poorer plan (for example , when different binds are peeked at hard parse). If the good plan still exists in memory, it is possible to force the query to use the good execution plan by creating a SPM baseline. If a SPM baseline is not usable, it is also possible to force a specific plan through a SQL profile created using the coe_xfr_sql_profile.sql script included in the SQLT Tool. Of course a rule of thumb to identify a good plan is to look at least CPU and/or elapsed time; unless you already know which plan suites you better on the long run!

For additional information on Baselines and Profiles, refer to:

Oracle Database Online Documentation 12c Release 1 (12.1)
Database Administration
Database SQL Tuning Guide
Chapter 23 Managing SQL Plan Baselines
23.1.1 Purpose of SQL Plan Management
23.1.1.2 Differences Between SQL Plan Baselines and SQL Profiles

http://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#TGSQL617

“SQL plan baselines reproduce a specific plan, whereas SQL profiles correct optimizer cost estimates.“

NOTE: In order to use SQL profiles an Oracle Tuning Pack licence is required. The Oracle Tuning Pack is available with Enterprise Edition ONLY.

For further details of pack licensing see:

Oracle Database Online Documentation 12c Release 1 (12.1)
Database Licensing Information

http://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC109

For information on SQLT see:

Document 215187.1 SQLT Diagnostic Tool

SQLT does not have to be installed in the database in order to use the "coe_xfr_sql_profile.sql" script. It can be run using the following steps:

Unzip sqlt.zip and navigate to the sqlt directory
Navigate to the subdirectory utl
Find the SQL_ID and the Plan Hash Value (PHV) for the plan you want to base the profile on. You can get the SQL_ID from SQLT in the "SQL Identification" section and the PHV from the "Execution Plans" section:

The SQL_ID is as follows:

The PHV is shown against the plans in the "Execution Plans" section:
Run the script coe_xfr_sql_profile.sql as SYSDBA user (ignore the 'Do not use SYS' in script) providing the sql_id and the good Plan Hash Value (PHV) :

SQL> START coe_xfr_sql_profile.sql <sql_id> <plan hash value for good plan>
Example:
SQL> START coe_xfr_sql_profile.sql 2qknbzqt0aoxb 365331166

where "2qknbzqt0aoxb" is the SQL ID for the problem query and "365331166" is the PHV (plan hash value) for the good plan
Step 4 generates a script named in the format: "coe_xfr_sql_profile_SQL_ID_PLAN_HASH_VALUE.sql" i.e. including the sql_id and plan hash value.

For example, with "2qknbzqt0aoxb" as the SQL ID and "365331166" as the PHV (as above) it will generate a script named: "coe_xfr_sql_profile_2qknbzqt0aoxb_365331166.sql"
If the query uses literals, but you would like to use the profile for all literals, you can modify the force_match parameter in the generated script from false to true:

force_match => TRUE

This will ensure the profile will be enabled even when different literals are used in the SQL query.
Run the generated script as SYSDBA user in order to correct the optimizer cost estimates used in the execution plan for the sql_id and encourage it to use the desired plan obtained from the stated plan hash value.
You can verify that the new execution plan is being used as followed:

select SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE from V$SQL where SQL_ID='2qknbzqt0aoxb';
The profile can be disabled or dropped as follows using the the SQL_PROFILE name returned from the query above:

To disable the profile:

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(Name => '<SQL PROFILE>', Attribute_Name => 'STATUS', Value => 'DISABLED');

To drop the profile:

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(Name => '<SQL PROFILE>');

NOTE: On RAC (Real Application Cluster) systems, the profile will be applicable on all instances.

How To Find Execution History Of An Sql_id | SQL Tuning

select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) +

extract(hour from (end_interval_time-begin_interval_time))*60

+ extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,

executions_delta executions,

round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b

where sql_id='&sql_id' and a.snap_id=b.snap_id

and a.instance_number=b.instance_number

order by snap_id desc, a.instance_number;

Using Sqltxplain To Create A 'SQL Profile' To Consistently Reproduce a Good SQL Plan (Doc ID 1487302.1) SQL TUNING best

In this Document

Goal

Solution

References

APPLIES TO:

Oracle E-Business Suite Performance - Version 11.5.10.2 to 12.2 [Release 11.5.10 to 12.2]
Information in this document applies to any platform.

GOAL

Manage site specific performance issues through the creation of a SQL profile for a statement that can be transferred to other systems.

SOLUTION

Introduction

The "cost-based optimizer (CBO or just optimizer) is the part of database software that determines the most efficient means of accessing the specified data (or at least what it "calculates" as the most efficient). The optimizer determines which execution plan is most efficient by considering several sources of information, including query conditions, available access paths, statistics gathered for the system, and hints.

Some changes in those conditions may affect the calculations of the plan and may lead the optimizer to produce sub-optimal plans, which may lead to slower performance of the statement(s) affected. There are several supported ways to try to influence the optimizer to produce the desired plan in EBS (like create custom indexes, gathering new stats for EBS tables or workload system stats), but a lot of those activities have the potential to influence more than just one statement.

10G and 11G have a feature called 'SQL Profile', which allows to the database to consistently apply the same plan (provided that you have it or had in the past) and as a result replicate the performance achieved in the past.

The benefits of using a SQL profile are that no changes to application code are necessary, only the targeted query is affected and the use of SQL profiles by the database is transparent to the end user.

SQL Profiles are useful if you have a limited number of SQL statements that are performing poorly after some change, like patching, database upgrade, etc.

Generally such issues should also be logged as E-Business Suite SR’s so that development can review whether the query can be better tuned – however running with the SQL profiles in place is a valid and supported workaround to get your system going again.

Types of 'SQL Profile'

There are 2 types of profiles, although currently there are no names to distinguish them

One that "fixes" the plan and produces exactly the same plan (this would be equivalent to the old 'stored outlines'). This is the type of profile that we will be using in this note.
One that does not "fix" the plan, but provide some hints such as "scaling factors" that allow the optimizer to produce a new plan if conditions change, like more data. This is typically what the SQL Tuning Advisor provides

Supportability

SQL profiles affect the performance, but not the result of a statement and should be transparent to the application's logic. EBS customers using SQL profiles that improve performance (of custom or seeded code) are supported.

Care should be taken that SQL profiles do not cause worse performance in your target database - for example under different data volume or usage patterns.

How to create a SQL Profile?

You can easily create a SQL profile for a statement using the script coe_xfr_sql_profile.sql provided with the tool Sqltxplain.

Refer to SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly Document 215187.1

Although the installation is NOT required (you can use the script directly), the installation of SQLT is a good idea as it will be useful when investigating performance problems.

This method requires that you have a RDBMS 10g/11g environment that currently has the SQL statement running with the good plan you want to consistently reproduce, or the good plan was chosen before and it is still in the AWR repository.

Some of the possible causes for SQL execution plan changes are:

Stats were gathered and the plan changed
A patch was applied or the DB was upgraded and the plan changed
A system was cloned to a different machine and the plan changed

How to recreate the plan?

In a nutshell:

Just run

SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];

This will create a script named like coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql

Execute the script generated in the system where you want to now reproduce the same plan

Details:

1) Download sqltxplain utility from

SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly Document 215187.1

The script named coe_xfr_sql_profile.sql is provided under the /utl directory.

2) Find the sql_id and the plan_hash_value (if there is more than one plan) for the statement that you want to create the profile against.

a) If you have a tkprof output in 11g, it may show this information right above the statement, like:

SQL ID: dnzjmxkbmk4uy
Plan Hash: 2517809292
select distinct spid from v$process p,v$session s where p.addr = s.paddr and s.audsid =userenv('sessionid')

b) If you have the raw sql trace, you can also search for that statement and you will see the sql_id in the parsing line right above the statement in question, as in the example below:

PARSING IN CURSOR #27 len=108 dep=0 uid=65 oct=3 lid=65 tim=1346257958769515 hv=2537100126 ad='5f9042b0' sqlid='dnzjmxkbmk4uy'
select distinct spid from v$process p,v$session s where p.addr = s.paddr and s.audsid = userenv('sessionid')

Having the sql_id, we can obtain the "plan_hash_value" running the statement below. Note that if there is more than one plan for the same SQL, it will show multiples per the example below.

WITH
p AS (
SELECT plan_hash_value
  FROM gv$sql_plan
 WHERE sql_id = TRIM('&&sql_id.')
   AND other_xml IS NOT NULL
 UNION
SELECT plan_hash_value
  FROM dba_hist_sql_plan
 WHERE sql_id = TRIM('&&sql_id.')
   AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
       SUM(elapsed_time)/SUM(executions) avg_et_secs
  FROM gv$sql
 WHERE sql_id = TRIM('&&sql_id.')
   AND executions > 0
 GROUP BY
       plan_hash_value ),
a AS (
SELECT plan_hash_value,
       SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
  FROM dba_hist_sqlstat
 WHERE sql_id = TRIM('&&sql_id.')
   AND executions_total > 0
 GROUP BY
       plan_hash_value )
SELECT p.plan_hash_value,
       ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
  FROM p, m, a
 WHERE p.plan_hash_value = m.plan_hash_value(+)
   AND p.plan_hash_value = a.plan_hash_value(+)
 ORDER BY
       avg_et_secs NULLS LAST;

Enter value for sql_id: dnzjmxkbmk4uy
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2517809292        .089

c) If you have just the sql_id from an AWR report, for example, you can use the statement above to find the plan_hash_value

d) Depending of the DB version, you may see just the 'hash_value' (hv) in the raw SQL trace (not to confuse with the plan_hash_value). You would see something like:

PARSING IN CURSOR #27 len=108 dep=0 uid=65 oct=3 lid=65 tim=1346257958769515 hv=2537100126 ad='5f9042b0'

Having the hash_value you can find the sql_id using

select sql_id from v$sql where hash_value = 'hash value from the trace';

SQL> select sql_id from v$sql where hash_value = '2537100126';
SQL_ID
-------------
dnzjmxkbmk4uy

and proceed as previous steps.

3) Having the sql_id and the plan_hash_id, run the following:

sqlplus / as sysdba
SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];

This will generate a script named like:
coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql

For example:
coe_xfr_sql_profile_dnzjmxkbmk4uy_2517809292.sql

This file contains the stored profile from the database that you obtained it from. You can now use this script to apply the same plan to another database:

4) Just execute this script in the DB where you want to create this profile

sqlplus / as sysdba
SQL> START coe_xfr_sql_profile_dnzjmxkbmk4uy_2517809292.sql

REFERENCES

NOTE:457531.1 - How to Move SQL Profiles from One Database to Another (Including to Higher Versions)
NOTE:215187.1 - All About the SQLT Diagnostic Tool
NOTE:271196.1 - Automatic SQL Tuning and SQL Profiles

Example: SQL Tuning Task Options (Doc ID 2461848.1) sql tuning

APPLIES TO:

Oracle Life Sciences Data Hub - Version 2.4.6 and later
Information in this document applies to any platform.

GOAL

This is an example in how to apply a SQL profile.

SOLUTION

In this example, let's assume the SQL IDs are cxp6f9nd21wy2 and fprmqtmh5uyjm. Then:

Method 1:

Step 1:

DECLARE
  l_sql_tune_task_id VARCHAR2(1000);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
  sql_id => 'cxp6f9nd21wy2',
  scope => DBMS_SQLTUNE.scope_comprehensive,
  time_limit => 120,
  task_name => 'cxp6f9nd21wy2',
  description => 'cxp6f9nd21wy2');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Step 2:

begin
DBMS_SQLTUNE.execute_tuning_task(task_name => 'cxp6f9nd21wy2');
end;
/

Step 3:

set lines 1000 pages 1000 long 1000000
column recommendations format a500
SELECT DBMS_SQLTUNE.report_tuning_task('cxp6f9nd21wy2') AS recommendations FROM dual;

Step 4: Final Step:

Accept profile or create base line.

execute dbms_sqltune.accept_sql_profile(task_name => 'cxp6f9nd21wy2', task_owner => 'SYS', replace => TRUE);
execute dbms_sqltune.CREATE_SQL_PLAN_BASELINE (task_name => 'cxp6f9nd21wy2', OBJECT_ID=> '', PLAN_HASH_VALUE=> '', OWNER_NAME=> '')

select sql_text, 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');' delete_sql_from_shared_pool from v$sqlarea where sql_id = 'cxp6f9nd21wy2';

Method 2:

Another option is to use the COE_XFR_SQL_PROFILE script - see NOTE:215187.1 "All About the SQLT Diagnostic Tool" for script and details.
This is a supported method of using SQL profiles with stored outlines style hints.

Step 1:

SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: fprmqtmh5uyjm

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1552755644 .003
1130512974 84.667

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1552755644

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID : "fprmqtmh5uyjm"
PLAN_HASH_VALUE: "1552755644"

SQL>BEGIN
  2 IF :sql_text IS NULL THEN
  3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4 END IF;
  5 END;
  6 /
SQL>SET TERM OFF;
SQL>BEGIN
  2 IF :other_xml IS NULL THEN
  3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4 END IF;
  5 END;
  6 /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_fprmqtmh5uyjm_1552755644.sql on TARGET system in order to create a custom SQL Profile with plan 1552755644 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

Step 2:

SQL> Execute coe_xfr_sql_profile_fprmqtmh5uyjm_1552755644.sql

Encouraging CBO to Pickup a Better Execution Plan Using the COE XFR SQL Profile Script SQL TUNING (Doc ID 1955195.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.2 and later
Information in this document applies to any platform.

GOAL

This Document helps in situations where a plan may have changed for some reason, but a good plan is still available and reproducible.

SOLUTION

http://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#TGSQL617

“SQL plan baselines reproduce a specific plan, whereas SQL profiles correct optimizer cost estimates.“

For information on SQLT see:

Document 215187.1 SQLT Diagnostic Tool

SQLT does not have to be installed in the database in order to use the "coe_xfr_sql_profile.sql" script. It can be run using the following steps:

Unzip sqlt.zip and navigate to the sqlt directory
Navigate to the subdirectory utl
Find the SQL_ID and the Plan Hash Value (PHV) for the plan you want to base the profile on. You can get the SQL_ID from SQLT in the "SQL Identification" section and the PHV from the "Execution Plans" section:

The SQL_ID is as follows:

The PHV is shown against the plans in the "Execution Plans" section:
Run the script coe_xfr_sql_profile.sql as SYSDBA user (ignore the 'Do not use SYS' in script) providing the sql_id and the good Plan Hash Value (PHV) :

SQL> START coe_xfr_sql_profile.sql <sql_id> <plan hash value for good plan>
Example:
SQL> START coe_xfr_sql_profile.sql 2qknbzqt0aoxb 365331166

where "2qknbzqt0aoxb" is the SQL ID for the problem query and "365331166" is the PHV (plan hash value) for the good plan
Step 4 generates a script named in the format: "coe_xfr_sql_profile_SQL_ID_PLAN_HASH_VALUE.sql" i.e. including the sql_id and plan hash value.

For example, with "2qknbzqt0aoxb" as the SQL ID and "365331166" as the PHV (as above) it will generate a script named: "coe_xfr_sql_profile_2qknbzqt0aoxb_365331166.sql"
If the query uses literals, but you would like to use the profile for all literals, you can modify the force_match parameter in the generated script from false to true:

force_match => TRUE

This will ensure the profile will be enabled even when different literals are used in the SQL query.
Run the generated script as SYSDBA user in order to correct the optimizer cost estimates used in the execution plan for the sql_id and encourage it to use the desired plan obtained from the stated plan hash value.
You can verify that the new execution plan is being used as followed:

select SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE from V$SQL where SQL_ID='2qknbzqt0aoxb';
The profile can be disabled or dropped as follows using the the SQL_PROFILE name returned from the query above:

To disable the profile:

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(Name => '<SQL PROFILE>', Attribute_Name => 'STATUS', Value => 'DISABLED');

To drop the profile:

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(Name => '<SQL PROFILE>');

NOTE: On RAC (Real Application Cluster) systems, the profile will be applicable on all instances.

REFERENCES

NOTE:215187.1 - All About the SQLT Diagnostic Tool

Good Plan Hash Value Not Showing in One of the RAC Node for Sqlid Even After Forcing Plan - SQL TUNING (Doc ID 2422536.1)

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

GOAL

Using "START coe_xfr_sql_profile.sql sqlid plan hash value"" on 3 node RAC, but do not see the new plan hash value on one off the nodes.

Using this ""SELECT * FROM TABLE(dbms_xplan.display_cursor('xxxxxxxxxx')) "" to check the plan hash value.

SOLUTION

Purging the sql id in question is a generic way to Since you were using the display_cursor command , it will show the plan which is in cursor.

Using DBMS_SHARED_POOL.PURGE procedure, you can flush a single SQL statement from the shared pool quite easily. The procedure call is overloaded to allow you to flush a named object using the address and hash_value found in V$SQLAREA. There are a couple of other call signatures that allow you to identify an object by schema and object name or hash value and namespace (to flush an object like a procedure or function) but the one we want for flushing a single SQL statement is the following:

DBMS_SHARED_POOL.PURGE
( name varchar2,
flag char default 'P',
heaps number deafult 1
)

If the SQL_ID of the statement is known, then can look up the cursor information to build the name parameter value using the following query:

SQL> select address, hash_value from gv$sqlarea where sql_id like 'XXXXXXXXX';

ADDRESS HASH_VALUE
---------------- ----------
00000000A9F34F98 1799024353

With the values, simply execute the call to the purge procedure:

SQL> exec dbms_shared_pool.purge('00000000A9F34F98, 1799024353','C'); >> C is for Cursor

PL/SQL procedure successfully completed.

Check the shared pool again after the purge successfully completes which should show no rows.

SQL> select address, hash_value from gv$sqlarea where sql_id like 'XXXXXXXXX';

no rows selected