Oracle add Sql Hints using Sql Patches

This   is  very less know  feature . Ideally most us are aware of sql profiles  to fix plan change how  this  is very less known which  can be used to  execution plan by enforcing hints when  changing code is not possible

Please note these will be applicable to single sql id , unlike  sql profile which is applicable to  sql signature,  In  case of  sql is keeps changing like in case on bind variable , we need to add  patch by modifying sql profile .



Create Sql patch :

 declare

   v_sql CLOB;

begin

   select sql_text into v_sql from dba_hist_sqltext where sql_id='bn7zqwkfgtr38';

   sys.dbms_sqldiag_internal.i_create_patch(

      sql_text  => v_sql,

      hint_text => 'DYNAMIC_SAMPLING(4)',

      name      => 'user_extents_patch');

end;

/







Listing commonly used hints in patch :

--> hint_text=>' IGNORE_OPTIM_EMBEDDED_HINTS'),
--> hint_text => 'PARALLEL(big_table,10)',
--> hint_text => 'PARALLEL(8)',

--> hint_text => 'OPT_PARAM("optimizer_index_caching" 80)',

--> hint_text => 'OPT_PARAM("optimizer_index_cost_adj" 1)',


Drop Sql Patches :

exec dbms_sqldiag.drop_sql_patch(name => 'my_patch');

PL/SQL procedure successfully completed.

check sql patch :

--> select name, status, created, sql_text from dba_sql_patches where name='user_extents_patch'

--> expalin plan:

Note ----- - SQL patch "PATCH_gz85dtvwaj4fw" used for this statemen







How to add hint when sql id changes :

When there are multiple sqlid in case of bind aware we have below 2 options

Option 1)

Modify your sql generated form COE script to add hint or add hint

to create sql profile statement . Optionally you can add hint to explain plan
statement with outline and use same outline to generate sql profile

DECLARE
     l_sql               clob;
     BEGIN
     l_sql := q'!select su_pk,su_name,su_comment,inner_view.maxamount from
                 t_supplier_su,
                 ( select max(or_totalamount) maxamount,su_fk from t_order_or group by su_fk ) inner_view
                 where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is not null!';

      dbms_sqltune.import_sql_profile( sql_text => l_sql, 
                                     name => 'SQLPROFILE_01',
                                     profile => sqlprof_attr(q'!USE_HASH_AGGREGATION(@"SEL$639F1A6F")!',
             q'!FULL(@"SEL$639F1A6F" "T_ORDER_OR"@"SEL$2")!',
             q'!USE_NL(@"SEL$1" "INNER_VIEW"@"SEL$1")!',
             q'!LEADING(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1" "INNER_VIEW"@"SEL$1")!',
             q'!NO_ACCESS(@"SEL$1" "INNER_VIEW"@"SEL$1")!',
             q'!FULL(@"SEL$1" "T_SUPPLIER_SU"@"SEL$1")!',
             q'!OUTLINE(@"SEL$1")!',
             q'!OUTLINE(@"SEL$2")!',
             q'!OUTLINE_LEAF(@"SEL$1")!',
             q'!PUSH_PRED(@"SEL$1" "INNER_VIEW"@"SEL$1" 1)!',
             q'!OUTLINE_LEAF(@"SEL$639F1A6F")!',
             q'!ALL_ROWS!',
             q'!DB_VERSION('11.2.0.3')!',
             q'!OPTIMIZER_FEATURES_ENABLE('11.2.0.3')!',
             q'!IGNORE_OPTIM_EMBEDDED_HINTS!'),
             force_match => true );
     end;
     /

Option 2 )

This is undocumented method to update sqlobj$ which your client will not allow   . First create a sql profile with any available

sql plan . Then update sqlobj$  to add int to sql profile

declare
pln_sql_id varchar2(20) :='4sdd3343222';
pln_plan_hash_value number := 2949544139;
orig_sql_id varchar2(20) := '4sdd3343222';
new_prof_name varchar2(20) := 'SQL_PROFILE_2';
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin
select extractvalue(value(d), '/hint') as outline_hints
bulk collect into ar_profile_hints
from xmltable('/*/outline_data/hint'
passing (select xmltype(other_xml) as xmlval from v$sql_plan
where sql_id = pln_sql_id and plan_hash_value = pln_plan_hash_value
and other_xml is not null)) d;
select sql_fulltext into cl_sql_text
from v$sql where sql_id = orig_sql_id;
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
name => new_prof_name,
force_match => true);
end;
  /

select comp_data from sqlobj$data where signature=(select signature from sqlobj$ where name='SQL_PROFILE_2');

- For 10.2, use the following sql to get profile hints :
-- select attr#, attr_val from sqlprof$attr where signature=(select signature from sqlprof$ where sp_name='SQL_PROFILE_2');

update sqlobj$data set 
comp_data='';
1 row updated.
--
-- For 10.2 use the below update statement :
-- update sqlprof$attr set ATTR_VAL='FULL(@"SEL$1" "EMP"@"SEL$1") 
NOPARALLEL(@"SEL$1" "EMP"@"SEL$1")' where attr#=5 and 
signature='784334333455334';
--
commit;
 alter system flush shared_pool;

Export import sql patch :

SQL> select name from dba_sql_patches; SQL> exec dbms_sqldiag.create_stgtab_sqlpatch('SQLPATCH_STAGE','SYSTEM'); SQL> exec dbms_sqldiag.pack_stgtab_sqlpatch(staging_table_name=>'SQLPATCH_STAGE', staging_schema_owner=>'SYSTEM'); export import -->> SQL> exec dbms_sqldiag.unpack_stgtab_sqlpatch(replace=>true, staging_table_name=>'SQLPATCH_STAGE', staging_schema_owner=>'SYSTEM');

Reference :

How to Create a SQL Patch to add Hints to Application SQL Statements (Doc ID 1931944.1)