Oracle SQL Profile

In Oracle Database, for a SQL-id, there are possible different plan hash values, and often times during the execution of the SQL, the execution plan picked a sub-optimal plan.

There is one way to create a SQL profile.

declare
ar_profile_xml clob;
cl_sql_text clob;
begin
select regexp_replace(other_xml,'.(.).*','\1')
into ar_profile_xml
from dba_hist_sql_plan -- this can be queried from gv$sql_plan too
where SQL_ID = 'awup6ytqb2p5c'
AND PLAN_HASH_VALUE = 3061475182 -- better execution plan identified from step 2
and other_xml is not null
and rownum =1;

select  sql_text into cl_sql_text
from    dba_hist_sqltext            -- this can be queried from gv$sql_plan too
where   sql_id = 'awup6ytqb2p5c'
and     dbid = (select dbid from v$database);

dbms_sqltune.import_sql_profile(
        sql_text => cl_sql_text ,
        profile_xml => ar_profile_xml ,
        name => 'PROF_awup6ytqb2p5c_3061475182');
end;
/

Collect Predicate Information from DBA_HIST_SQL_PLAN or GV$SQL_PLAN and load it as the SQL profile

#####################
select  	CHR(9)||'''' ||regexp_replace(extractvalue(value(d),'/hint'),'''','''''')  || ''','
from    	xmltable('/*/outline_data/hint'
                	PASSING (SELECT    XMLTYPE(OTHER_XML) AS XMLVAL
                        	FROM      DBA_HIST_SQL_PLAN		 
                        	WHERE    SQL_ID = '<SQLID>'
                        	AND         PLAN_HASH_VALUE = <PLAN_HASH_VALUE>
                        	and         other_xml is not null))  d;

######################

declare

	ar_profile_hints sys.sqlprof_attr;
	l_sql               clob;

begin
	ar_profile_hints := sys.sqlprof_attr(
      		'INDEX_RS_ASC(@"SEL$1" "PHI"@"SEL$1" ("PO_HEADERS_INTERFACE"."BATCH_ID"))',
      		'OUTLINE_LEAF(@"SEL$1")',
      		'ALL_ROWS',
'OPT_PARAM("_replace_virtual_columns" "false")',
      		'OPT_PARAM("_gby_hash_aggregation_enabled" "false")',
      		'OPT_PARAM("_optimizer_squ_bottomup" "false")',
      		'OPT_PARAM("_fast_full_scan_enabled" "false")',
      		'OPT_PARAM("_b_tree_bitmap_plans" "false")',
      		'DB_VERSION("11.2.0.4")',
      		'OPTIMIZER_FEATURES_ENABLE("11.2.0.4")',
      		'IGNORE_OPTIM_EMBEDDED_HINTS'
);

	select sql_text into l_sql FROM DBA_HIST_SQLTEXT where sql_id = '<SQLID>' and rownum = 1;
	-- or
	-- select sql_fulltext into l_sql FROM gv$sql where sql_id = '<SQLID>' and rownum = 1;
	
	DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
	sql_text    => l_sql,
	profile     => ar_profile_hints,
	name	    => 'PROF_<SQLID>_<PHV>');

end;
/

Patching a SQL without modifying the SQL, by including aHINT and creating SQL profile

DECLARE
     l_sql               clob;
BEGIN
     select sql_text into l_sql FROM DBA_HIST_SQLTEXT where sql_id = 'g4h1n4f5jxnbd' and rownum = 1;
     dbms_sqltune.import_sql_profile( sql_text => l_sql,
                                     name => 'PROF_g4h1n4f5jxnbd',
                                     profile => sqlprof_attr('index(EMP,XXINT_EMP_DETAILS_MV_N1)'));
end;
/

Check if SQL-profile is working as expected.

select       inst_id, executions, trunc(elapsed_time/1000000/executions,2) avg_time_sec, plan_hash_value, child_number, sql_profile, users_executing, sql_fulltext
from        gv$sql
where      sql_id = ‘<sql_id>’;

If we need to disable the sql-profile for any reason

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_02817ede14a30000',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/

If we need to rename the sql_profile to a more meaningful name

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE (
name => 'SYS_SQLPROF_0281add623180004',
attribute_name => 'NAME',
value => 'PROF_dfurkmh438uv5_2882205380');
END;
/