Create a SQL Patch to add Hints optimizer_features_enable to SQL Statements in Oracle

Use SQL Patch on hint optimizer_features_enable for SQL Statement fix Performance Issue in Oracle

Step 1: Enable the autotrace on

SET LINE 200 PAGES 200
SET AUTOTRACE ON
Select * from hr.test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 73923 |  4403K|   194   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST | 73923 |  4403K|   194   (2)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5572  consistent gets
        677  physical reads
          0  redo size
    5861899  bytes sent via SQL*Net to client
      54591  bytes received via SQL*Net from client
       4930  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73923  rows processed

Step 2: Identify the SQL_ID of the query you want to patch. You can find this using the V$SQL view.

SELECT sql_id, sql_text 
FROM v$sql 
WHERE sql_text LIKE '%your_query_text%';

Example:
SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE '%select * from hr.test%';

SQL_ID         SQL_TEXT
-------------  -------------------------------
0wm3d81zgj7m2 select * from hr.test

Step 3: Create SQL Patch using the optimizer_features_enable hint for the Query with help of SQL Patch:

SQL> variable x varchar2(100);
SQL> exec :x:=dbms_sqldiag.create_sql_patch(sql_id=>'0wm3d81zgj7m2', hint_text=>'optimizer_features_enable(''11.2.0.3'')', name=> 'SQL_Patch_11.2.0.3');

Step 4: Verify the SQL Patch is used or not:

Step 5: Verify the existence of SQL Patch:

select name from dba_sql_patches;

Step 6: If you donot need the SQL Patch then delete/drop it.

SQL> exec DBMS_SQLDIAG.drop_sql_patch(name => 'SQL_Patch_11.2.0.3');