SQL Patch and Force Match

 how to create sql_patch with force_match=true in Oracle 11g



1)  how to create a SQL_PATCH from command line 

begin

 sys.dbms_sqldiag_internal.i_create_patch

  (sql_text  => '..put SQL here..',

   hint_text => '..put hints here..',

   name      => 'my patch name'); 

end;

/

2) What if I have a sql_id instead of the sql_text?

SQL> var c clob

SQL> exec select sql_fulltext into :c from v$sqlstats where sql_id='...' and rownum=1;

begin

 sys.dbms_sqldiag_internal.i_create_patch

  (sql_text  => :c,

   hint_text => '..put hints here..',

   name      => 'my patch name'); 

end;

/

3) What if I want to create a SQL patch with FORCE_MATCH = TRUE?
(force match will additionally target all SQL statements that have the same text after normalizing literal values to bind variables). 

-- create sql patch

-- allows to set FORCE_MATCH to TRUE which is currently not possible with dbms_sqldiag_internal.i_create_patch (11.2.0.3)

-- run as sys

-- this is undocumented stuff, handle with care

DECLARE

    sql_text clob            := '....put sql here...';

    hints    varchar2(1000)  :='....put hints here...';

    description varchar2(100):='my patch description';

    name varchar2(100)       :='my patch name';

    output   varchar2(100);

    sqlpro_attr SYS.SQLPROF_ATTR;

  

BEGIN

    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;

/

4) Additional considerations:

What should I put as hints in the SQL PATCH?
I would normally use the full outline content from an execution plan that I have tested and found OK. Mileage may vary. To print out the outline one can use:
select * from table(dbms_xplan.display('PLAN_TABLE',null,'OUTLINE'));

Drop and enable/disable
SQL patches with: DBMS_SQLDIAG.DROP_SQL_PATCH and DBMS_SQLDIAG.ALTER_SQL_PATCH

Diagnostics: 
dba_sql_patches. That view is based on tables shared with sql profiles and sql baselines. Underlying tables of interest for baselines, profiles and sql patches are: sys.sqlobj$data sys.sqlobj$auxdata, sys.sql$text, sys.sqlobj$
V$SQL.SQL_PATCH when not null reports the name of the sql patch that has been used to parse that particular child cursor.


Shabbir Ahmad