SQL Patch and Force Match

Discussion on how to create sql_patch with force_match=true in Oracle 11g and related topics.

SQL patches have recently saved the day for me in a production issue where a given SQL had suddenly changed execution plan causing IO overload (a full scan was done instead of index-based read for a high-load statement). 11g allows for a quick fix in such situations (as in, stop the fire and buy time to find a more stable solution): a set of hints can be added to a given query via the use of SQL Patch.
The official Oracle documentation has not many details on the topic (up to 11.2.0.3). Oracle's optimizer blog and Dominic Brooks have very good posts on the topic though.
I add here a few additional details that I have researched and found useful:


1) Recap (from the references listed above): 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.