Oracle: SQL Patch (for injecting hints into an SQL Statement)

Example

create table tq84_A (
   col_1 number,
   col_2 varchar2(10),
   col_3 date
);

create index tq84_A_ix on tq84_A(col_2);

Execute a query.

select * from tq84_A t where col_2 like 'ABC%';

select *
from
   dbms_xplan.display_cursor(
       format => 'advanced +alias',
       sql_id =>  dbms_sql_translator.sql_id(q'[select * from tq84_A t where col_2 like 'ABC%']')
   );

Unsurprisingly, the query used the index.

-- --------------------------------------------------------------------------------------
-- | Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)|
-- --------------------------------------------------------------------------------------
-- |   0 | SELECT STATEMENT                    |           |       |       |     1 (100)|
-- |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TQ84_A    |     1 |    29 |     0   (0)|
-- |*  2 |   INDEX RANGE SCAN                  | TQ84_A_IX |     1 |       |     0   (0)|
-- --------------------------------------------------------------------------------------

Thanks to using advanced +alias in dbms_xplan.display_cursor, the output also shows the query block aliases which are helpful when creating the SQL patch:

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / "T"@"SEL$1"
   2 - SEL$1 / "T"@"SEL$1"

Trying to patch the SQL statement so that it does not use index anymore…

declare
   patch_name varchar2(100);
begin
   patch_name :=
      sys.dbms_sqldiag.create_sql_patch(
        name      => 'TQ84_A_NO_INDEX',
        sql_id    =>  dbms_sql_translator.sql_id(q'[select * from tq84_A t where col_2 like 'ABC%']'),
        hint_text => 'FULL(@"SEL$1" "T"@"SEL$1")'
     );
end;
/
   

select * from dba_sql_patches where name = 'TQ84_A_NO_INDEX';

Run the statement again …

select * from tq84_A t where col_2 like 'ABC%';

… and show its cursor. Note, it does not use the index anymore:

select *
from
   dbms_xplan.display_cursor(
       format => 'advanced +alias',
       sql_id =>  dbms_sql_translator.sql_id(q'[select * from tq84_A t where col_2 like 'ABC%']')
   );
--
-- ----------------------------------------------------------------------------
-- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-- ----------------------------------------------------------------------------
-- |   0 | SELECT STATEMENT  |        |       |       |     2 (100)|          |
-- |*  1 |  TABLE ACCESS FULL| TQ84_A |     1 |    29 |     2   (0)| 00:00:01 |
-- ----------------------------------------------------------------------------

Further down in the output, we find:

Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('21.1.0')
      DB_VERSION('21.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

More queries:

select
   obj.name,
   dat.comp_data,
   dat.category,
   obj.last_executed,
   txt.sql_handle,
   sql.inuse_features,
   sql.flags,
   txt.sql_text
from
   sys.sql$        sql                                        join
   sys.sql$text    txt on sql.signature = txt.signature       join
   sys.sqlobj$     obj on sql.signature = obj.signature       join
   sys.sqlobj$data dat on obj.signature = dat.signature and
                          obj.category  = dat.category
where
-- d.signature = dbms_sqltune.sqltext_to_signature(q'[select * from tq84_A t where col_2 like 'ABC%']')
   obj.name = 'TQ84_A_NO_INDEX'
;

select * from sys.sqlobj$plan;

select * from v$sql where sql_patch = 'TQ84_A_NO_INDEX';

Cleaning up

begin
   sys.dbms_sqldiag.drop_sql_patch('TQ84_A_NO_INDEX');
end;
/

Oracle: SQL Patch (for injecting hints into an SQL Statement)

Example

See also