SQL PATCH with force_matching

output.sql

SQL> @tests/patch_fm.sql
SQL> create table test(n,x, constraint test_pk primary key(n))
  2   as select level n, level x from dual connect by level<=100;

Table created.

SQL> set serverout on;
SQL> declare
  2    res varchar2(4000);
  3  begin
  4    res:= sys.dbms_sqltune_internal.i_create_sql_profile(
  5              SQL_TEXT      --IN CLOB
  6                  => 'select * from test t where t.n=1'
  7             ,PROFILE_XML   --IN CLOB
  8                  => sys.DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(
  9                        sys.sqlprof_attr(
 10                           'full(t@sel$1)'
 11                          ,'first_rows(10)'
 12                        ))
 13             ,NAME          --IN VARCHAR2 := NULL,
 14                  => 'patch_test'
 15             ,DESCRIPTION   --IN VARCHAR2 := NULL,
 16                  => 'test desc'
 17             --,CATEGORY      --IN VARCHAR2 := NULL,
 18             --,CREATOR       --IN VARCHAR2 := NULL,
 19             ,VALIDATE      --IN BOOLEAN  := TRUE,
 20                  => false
 21             --,REPLACE       --IN BOOLEAN  := FALSE,
 22             ,FORCE_MATCH   --IN BOOLEAN  := FALSE,
 23                  => true
 24             ,TYPE          --IN VARCHAR2 := NULL,
 25                  => 'PATCH'
 26             ,IS_PATCH      --IN BOOLEAN  := FALSE,
 27                  => true
 28             --,PLAN_ROWS     --IN SQL_PLAN_TABLE_TYPE := NULL
 29         );
 30     dbms_output.put_line(res);
 31  end;
 32  /
patch_test

PL/SQL procedure successfully completed.

SQL> set serverout off;
SQL> select * from test t where t.n=50;

         N          X
---------- ----------
        50         50

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor('','','advanced'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dg1nqms8x6uc0, child number 0
-------------------------------------
select * from test t where t.n=50

Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     6 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T"."N"=50)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T"."N"[NUMBER,22], "T"."X"[NUMBER,22]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  first_rows(10)

   1 -  SEL$1 / T@SEL$1
           -  full(t@sel$1)

Note
-----
   - SQL patch "patch_test" used for this statement

Query Block Registry:
---------------------

  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T]]></t><s><![C
        DATA[SEL$1]]></s></h></f></q>



63 rows selected.

SQL> exec dbms_sqldiag.drop_sql_patch('patch_test');

PL/SQL procedure successfully completed.

SQL> drop table test;

Table dropped.

SQL> set echo off;

test.sql

set echo on;
create table test(n,x, constraint test_pk primary key(n))
 as select level n, level x from dual connect by level<=100;
set serverout on;
declare
  res varchar2(4000);
begin
  res:= sys.dbms_sqltune_internal.i_create_sql_profile(
            SQL_TEXT      --IN CLOB
                => 'select * from test t where t.n=1'
           ,PROFILE_XML   --IN CLOB
                => sys.DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(
                      sys.sqlprof_attr(
                         'full(t@sel$1)'
                        ,'first_rows(10)'
                      ))
           ,NAME          --IN VARCHAR2 := NULL,
                => 'patch_test'
           ,DESCRIPTION   --IN VARCHAR2 := NULL,
                => 'test desc'
           --,CATEGORY      --IN VARCHAR2 := NULL,
           --,CREATOR       --IN VARCHAR2 := NULL,
           ,VALIDATE      --IN BOOLEAN  := TRUE,
                => false
           --,REPLACE       --IN BOOLEAN  := FALSE,
           ,FORCE_MATCH   --IN BOOLEAN  := FALSE,
                => true
           ,TYPE          --IN VARCHAR2 := NULL,
                => 'PATCH'
           ,IS_PATCH      --IN BOOLEAN  := FALSE,
                => true
           --,PLAN_ROWS     --IN SQL_PLAN_TABLE_TYPE := NULL
       );
   dbms_output.put_line(res);
end;
/
set serverout off;
select * from test t where t.n=50;
select * from table(dbms_xplan.display_cursor('','','advanced'));
exec dbms_sqldiag.drop_sql_patch('patch_test');
drop table test;
set echo off;