RSA Product Set: RSA Governance & Lifecycle
RSA Version: 7.5.0 and above
Oracle Version: 11g and above

This knowledge base entry is to be used when advised by RSA Engineering that there is a need to create an SQL Profile in Oracle to improve performance of a particular query by providing the Oracle optimizer with better execution plans.

Why Do We Create an SQL Profile in Oracle?

An SQL Profile is created in Oracle Database to improve query performance by providing the optimizer with better execution plans. It helps correct suboptimal execution plans without modifying the SQL statement itself.

What is PLAN_HASH_VALUE in Oracle?

PLAN_HASH_VALUE is a numerical identifier that Oracle assigns to an execution plan. It helps identify whether a SQL query uses the same execution plan over time or if the optimizer is generating different plans, which may impact performance.

1) Choose on of the following methods to identify which query is taking long time: 
    a.) Using an AWR report , go to Elapsed-time section and look for the query that took the maximum execution time.

For example: 

b.) The following query can also help to find SQL_ID of user: 

select SQL_ID from gv$sql where sql_fulltext like '%User Query%';

2) Once you determine the SQL_ID of the query, use the following query to get plan_hash_value and response time based on the SQL_ID.

SELECT   inst_id,
         sql_id,
         plan_hash_value,
         elapsed_time,
         executions,
         rows_processed,
         ROUND (rows_processed / executions, 0)
             "ROWS_PER_EXEC",
         TO_CHAR (elapsed_time / 1000000 / executions, '999,999.99')
             avg_sec,
         last_active_time,
         last_load_time,
         sql_profile,
         sql_plan_baseline
    FROM gv$sql
   WHERE sql_id = 'SQL_ID of your query'
     AND executions > 0
ORDER BY last_active_time, inst_id;

3) Select plan_hash_value that contains the minimum average seconds.

Example :

Plan_hash_value              AVG_SEC ( Average Response Time)

584201484                      1.72 seconds

876057594                      17.7 seconds

4) Create SQL Profile for SQL_ID ( e.g. aruas4y5jn212 ) with plan_hash_value in the database. Using the example above, the average response time of plan_hash_value (e.g.584201484 ) is 1.72 seconds. Once SQL profile is created for this SQL_ID, it will remove blocking session created by this query.

Use the steps below to create a SQL profile for any given query.

Please Refer to Oracle Support and look for the article SQLT Diagnostic Tool (Doc ID 215187.1). 
Using the link within the article, download and save the SQLT tool. After unzip the file, and go to sqlt>utl and find a script called coe_xfr_sql_profile.sql

Note(s): 

• In order to use this script, you DO NOT NEED to have SQLT installed. Just add this script to your collection of scripts, and use it as needed.
• If you are unable find the coe_xfr_sql_profile.sql from Oracle support, please reach out to RSA Support to get a copy of the coe_xfr_sql_profile.sql

• These scripts cannot be executed using from SQL Developer tool. Please use SQLPLUS command prompt to Oracle server using a SYS credential to the appropriate environment. 

4a) Using a copy coe_xfr_sql_profile.sql under $ORACLE_HOME\rdbms\admin directory, run below command at SQL prompt

SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)
Enter value for 1:  

Enter the SQL id from step 1 here.

It will show the available plan_hash_value with AVG_ET_SECS

Parameter 2:   
PLAN_HASH_VALUE (required)   ( e.g.Selected plan_hash_value )
Enter value for 2:

It will generate a file which you need to execute to fix the hash value plan for particular SQL ID. It will create a SQL Profile.

4b) Run below command at SQL prompt 

SQL> @coe_xfr_sql_profile_sqlid_planhashvalue.sql;

4c) The following query can be used to verify if the SQL profile is created:

select p.name,s.sql_id
from dba_sql_profiles p,DBA_HIST_SQLSTAT s
where p.name=s.sql_profile
and s.sql_id='SQL_ID of user query';