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;

/




Or







DECLARE l_sql_text CLOB :='select /*+ no_index(emp pk_emp) */ * from emp where empno=7839'; l_sql_id VARCHAR2(30) :='???'; l_sql_patch_hints VARCHAR2(500):=q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]'; l_sql_patch_name VARCHAR2(30) :='my_sql_patch'; l_sql_patch_desc VARCHAR2(500):=l_sql_patch_name || 'my_sql_patch_description'; l_output varchar2(100); BEGIN IF (l_sql_text IS NULL) THEN SELECT sql_fulltext INTO l_sql_text FROM v$sqlarea WHERE sql_id = l_sql_id AND ROWNUM < 2; END IF; l_output:= SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch( sql_text => l_sql_text , hint_text => l_sql_patch_hints , name => l_sql_patch_name , description => l_sql_patch_desc ); 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)',



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

Drop Sql Patches :

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

PL/SQL procedure successfully completed.

Check if sql patch is used :

Select sql_id , SQL_PATCH , PLAN_HASH_VALUE from v$sql ;

--> expalin plan:

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







Creating Sql patch using Awr :




>> let’s get the ORIGINAL sql_text var c clob exec SELECT trim(sql_text) INTO :c FROM dba_hist_sqltext WHERE sql_id='1pzcw65khq4tu' AND rownum=1; >> let’s get the sql_text for the query which has the hint, again, from the AWR views: var h clob exec SELECT - listagg(hint,' ') within group(order by rownum) into :h - FROM - ( - SELECT - b.hint - FROM - dba_hist_sql_plan m - ,xmltable ( - '/other_xml/outline_data/hint' passing xmltype (m.OTHER_XML) columns hint clob PATH '/hint' - ) b - WHERE - TRIM( OTHER_XML ) IS NOT NULL - AND sql_id = 'gs6gf69grvbbt' - AND plan_hash_value = '2839797856' - ); / >> let’s create the SQL Patch to add the hint into the query: var x varchar2(100); begin :X:=dbms_sqldiag_internal.I_CREATE_PATCH(SQL_TEXT => :C,HINT_TEXT => :h,CREATOR=>'SYS',NAME => '&SQLPATCH_NAME'); end; /
















Creating Sql patch with force option : DECLARE sql_text clob ; hints varchar2(1000) :='&&2'; description varchar2(100):='created on ' || &&CURDAT; name varchar2(100) :='&&PATCHNAME'; output varchar2(100); sqlpro_attr SYS.SQLPROF_ATTR; BEGIN dbms_output.enable(null); select sql_text into sql_text from dba_hist_sqltext where sql_id='&&1' and rownum=1; sqlpro_attr := SYS.SQLPROF_ATTR(hints); output := SYS.DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE( SQL_TEXT => sql_text, PROFILE_XML => DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(sqlpro_attr), NAME => name, DESCRIPTION => description, CATEGORY => 'DEFAULT', CREATOR => 'SYS', VALIDATE => TRUE, TYPE => 'PATCH', FORCE_MATCH => TRUE, IS_PATCH => TRUE); dbms_output.put_line(output); END; /













==> To Add  mmultiple Hints inside  sql patch we need to add hints seperate with space 

Apart from optimizer parameter we can use sql patch to add access patch hints

SET SERVEROUTPUT ON

DECLARE

  l_sql_text        CLOB         :='select /*+ NO_index(emp pk_emp) */ * from EMP where empno=7839';

  l_sql_id          VARCHAR2(30) :='7a1dd4z42gw7t';

  l_sql_patch_hints VARCHAR2(500):=q'[INDEX(@"SEL$1" "EMP"@"SEL$1") OPT_PARAM('optimizer_dynamic_sampling' 0)]';

  l_sqlpro_attr SYS.SQLPROF_ATTR :=SYS.SQLPROF_ATTR(l_sql_patch_hints);

  l_sql_patch_name  VARCHAR2(30) :='my_sql_patch';

  l_sql_patch_desc  VARCHAR2(500):=l_sql_patch_name || 'my_sql_patch_description';

  l_output   varchar2(100);

BEGIN

  IF (l_sql_text IS NULL) THEN

    SELECT sql_fulltext INTO l_sql_text FROM v$sqlarea WHERE sql_id = l_sql_id AND ROWNUM < 2;

  END IF;

  l_output := SYS.DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE(

    SQL_TEXT    => l_sql_text,

    PROFILE_XML => DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(l_sqlpro_attr),

    NAME        => l_sql_patch_name,

    DESCRIPTION => l_sql_patch_desc,

    CATEGORY    => 'DEFAULT',

    CREATOR     => 'SYS',

    VALIDATE    => TRUE,

    TYPE        => 'PATCH',

    FORCE_MATCH => TRUE,

    IS_PATCH    => TRUE);

    DBMS_OUTPUT.PUT_LINE(l_output);

END;

/

How to Find the SQL Text and Hints Stored in SQL Patch

set pagesize 60 set linesize 300 set trimspool on column sql_text format a40 column plan_name format a30 column signature format 999999999999999999999 column hint format a50 wrap word select prf.plan_name, prf.sql_text, prf.signature, extractvalue(value(hnt),'.') hint from ( select so.name plan_name, so.signature, so.category, so.obj_type, so.plan_id, st.sql_text, sod.comp_data from sqlobj$ so, sqlobj$data sod, sql$text st where sod.signature = so.signature and st.signature = so.signature and st.signature = sod.signature and sod.category = so.category and sod.obj_type = so.obj_type and sod.plan_id = so.plan_id and so.obj_type = 3 and so.name = 'SQL_Patch_test1' order by signature, obj_type, plan_id ) prf, table ( select xmlsequence( extract(xmltype(prf.comp_data),'/outline_data/hint') ) from dual ) hnt;







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)

NOTE:2444753.1 - How To Create SQL Patch From AWR Repository Without Merging Outline Data Hints Manually 

NOTE:1931944.1 - How to Create a SQL Patch to add Hints to Application SQL Statements 

 How to Find the SQL Text and Hints Stored in SQL Patch (Doc ID 2543795.1)