How to implement custom Sql Profile in Oracle

We all know about SQL profiles in Oracle and How they add hints and scaling factors that help in the Performance of the sql in Oracle.

We have two types of sql profile

1. This is the standard one that we get from sql tuning advisor. It tunes the sql statement using hint and scaling factors. It does not fix the plan of the sql
2. This is the custom sql profile that fix the plan of the sql statement. This would be equivalent to the old ‘stored outlines

I would be giving more details on the second one in this post

How to create the custom sql profile

• Oracle provides a script in the sqltxplan tool. The script name is coe_xfr_sql_profile.sql
• As such we dont require the installation of the sqltxplain tool to make it work, but it is good practice to install it
• The script is located in <sqlt folder>/utl location
• For this to be useful, we should have a good plan you want to consistently reproduce, or the good plan was chosen before and it is still in the AWR repository.

How to execute it

Step 1
First, execute the script coe_xfr_sql_profile.sql

sqlplus / as sysdba
@coe_xfr_sql_profile.sql

It will prompt you to the sql_id whose plan you want to fix

Step 2

Upon giving the sql_id, the script will present you all the plan_hash_value and their execution time. You can choose the plan_hash_value of the good plan and then press enter

This will create a script named like coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql in your current folder

Step 3

You can run the sql just created on the same database or other databases where you want that plan_hash_value to get fixed

sqlplus / as sysdba
@coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql

How to find all plan_hash_value of the sql_id

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;

How to check if the sql_id is using the sql profile

select SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE from V$SQL where SQL_ID='&sql_id';

How to disable the profile

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

I hope this is useful to you. Please do provide the feedback

How to move the sql profiles in Oracle
how to check sql profile in oracle
sql tuning advisor