create_sql_patch

set serveroutput on

declare
  l_sql varchar2(1000) := q'[select count(*) from all_users]';
  l_res varchar2(100);
begin
  l_res := sys.dbms_sqldiag.create_sql_patch(
    sql_text  => l_sql,
    hint_text => q'[optimizer_features_enable('11.2.0.4')]',
    name      => 'tiak_test'
  );
  dbms_output.put_line(l_res);
end;
/


===

SQL_ID  b2zqhgr5tzbpk, child number 0
-------------------------------------
select count(*) from all_users

Plan hash value: 2934650950

--------------------------------------------------
| Id  | Operation             | Name    | E-Rows |
--------------------------------------------------
|   0 | SELECT STATEMENT      |         |        |
|   1 |  SORT AGGREGATE       |         |      1 |
|*  2 |   INDEX FAST FULL SCAN| I_USER2 |    285 |
--------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('19.1.0')
      OPT_PARAM('_px_adaptive_dist_method' 'choose')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'true')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'true')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2" >"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_FFS(@"SEL$F5BB74E1" "U"@"SEL$2" ("USER$"."USER#" "USER$"."TYPE#"
              "USER$"."SPARE1" "USER$"."SPARE2"))
      END_OUTLINE_DATA
  */

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

   2 - filter("U"."TYPE#"=1)

Note
-----
   - SQL patch "tiak_test" used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


47 rows selected.


SQL_ID  bs8msr9g2a6zr, child number 0
-------------------------------------
SELECT COUNT(*) FROM ALL_USERS

Plan hash value: 2934650950

--------------------------------------------------
| Id  | Operation             | Name    | E-Rows |
--------------------------------------------------
|   0 | SELECT STATEMENT      |         |        |
|   1 |  SORT AGGREGATE       |         |      1 |
|*  2 |   INDEX FAST FULL SCAN| I_USER2 |    285 |
--------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('19.1.0')
      OPT_PARAM('_px_adaptive_dist_method' 'choose')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'true')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'true')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2" >"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_FFS(@"SEL$F5BB74E1" "U"@"SEL$2" ("USER$"."USER#" "USER$"."TYPE#"
              "USER$"."SPARE1" "USER$"."SPARE2"))
      END_OUTLINE_DATA
  */

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

   2 - filter("U"."TYPE#"=1)

Note
-----
   - SQL patch "tiak_test" used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


47 rows selected.


SQL_ID  6rdp5awykj73b, child number 0
-------------------------------------
select /* test */ count(*) from all_users

Plan hash value: 3622317471

---------------------------------------------
| Id  | Operation        | Name    | E-Rows |
---------------------------------------------
|   0 | SELECT STATEMENT |         |        |
|   1 |  SORT AGGREGATE  |         |      1 |
|*  2 |   INDEX SKIP SCAN| I_USER2 |    285 |
---------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('_fast_full_scan_enabled' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2" >"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_SS(@"SEL$F5BB74E1" "U"@"SEL$2" ("USER$"."USER#" "USER$"."TYPE#"
              "USER$"."SPARE1" "USER$"."SPARE2"))
      END_OUTLINE_DATA
  */

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

   2 - access("U"."TYPE#"=1)
       filter("U"."TYPE#"=1)

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


46 rows selected.


SQL_ID  2djphr73jrxvx, child number 0
-------------------------------------
select    COUNT(*)   from    all_users

Plan hash value: 2934650950

--------------------------------------------------
| Id  | Operation             | Name    | E-Rows |
--------------------------------------------------
|   0 | SELECT STATEMENT      |         |        |
|   1 |  SORT AGGREGATE       |         |      1 |
|*  2 |   INDEX FAST FULL SCAN| I_USER2 |    285 |
--------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('19.1.0')
      OPT_PARAM('_px_adaptive_dist_method' 'choose')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'true')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'true')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2" >"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_FFS(@"SEL$F5BB74E1" "U"@"SEL$2" ("USER$"."USER#" "USER$"."TYPE#"
              "USER$"."SPARE1" "USER$"."SPARE2"))
      END_OUTLINE_DATA
  */

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

   2 - filter("U"."TYPE#"=1)

Note
-----
   - SQL patch "tiak_test" used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


47 rows selected.

SQL> select force_matching_signature, exact_matching_signature, sql_id from v$sqlarea where sql_id in ('2djphr73jrxvx', '6rdp5awykj73b', 'bs8msr9g2a6zr', 'b2zqhgr5tzbpk');

FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE SQL_ID
------------------------ ------------------------ -------------
     5693229891079148371      5693229891079148371 6rdp5awykj73b
     5932186539561753882      5932186539561753882 bs8msr9g2a6zr
     5932186539561753882      5932186539561753882 b2zqhgr5tzbpk
     5932186539561753882      5932186539561753882 2djphr73jrxvx

SQL>