SQL Patch Utility

From 11.2.0.3 the SQL Patch utility was introduced to control SQL statements by adding hints but without changing the SQL code. The process for setting this up is extremely simple.

SQL> exec sys.dbms_sqldiag_internal.i_create_patch(

  sql_text => 'select count(*), max(empno) from emp where deptno = :deptno',

    hint_text => 'BIND_AWARE',

    name =>'My_Patch');

The value for “hint_text” can be any valid hint.

If you want to include multiple hints, you can do so just by separating the hints with spaces. If you have a hint that require quotes such as

optimizer_features_enable('11.2.0.4')

don’t forget to ‘protect’ the quotes by putting two quotes (see below)

SQL> exec sys.dbms_sqldiag_internal.i_create_patch(

  sql_text=>'select count(*) from sales',

  hint_text=>'BIND_AWARE optimizer_features_enable(''11.2.0.4'')',

  name=>'MYPATCH');

This results in the following plan#

Execution Plan

----------------------------------------------------------

Plan hash value: 3730320257

------------------------------------------------------------------------------------

| Id  | Operation                      | Name              | Rows  | Cost (%CPU)| Ps

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |                   |     1 |     0   (0)|

|   1 |  SORT AGGREGATE                |                   |     1 |            |

|   2 |   PARTITION RANGE ALL          |                   |   918K|            |

|   3 |    BITMAP CONVERSION COUNT     |                   |   918K|            |

|   4 |     BITMAP INDEX FAST FULL SCAN| SALES_CHANNEL_BIX |       |            |

------------------------------------------------------------------------------------