Use SQL PATCH in SQL Statements for fixed performance issues in Oracle

SQL Patch is used to increasing SQL performance by using hints without changing the application code.

Suppose a certain query is causing slowness and we have put some specific hint and it will fix the issue but for that, we need to change the code in the application.

We need a lot of things to do it. To overcome this, DBA can create an SQL Patch for specific SQL queries to use the specific hints.

Syntax of Create or Drop SQL Patch:

-- Create the SQL Patch syntax:
DBMS_SQLDIAG.CREATE_SQL_PATCH (
 sql_text  IN   CLOB,
 hint_text IN   CLOB,
 name  IN   VARCHAR2  := NULL,
description IN VARCHAR2:= NULL,
category IN VARCHAR2 := NULL,
validate IN BOOLEAN := TRUE)
RETURN VARCHAR2; 

DBMS_SQLDIAG.CREATE_SQL_PATCH (
sql_id IN   VARCHAR2,
hint_text   IN   CLOB,
name IN   VARCHAR2 := NULL,
description IN VARCHAR2 :=NULL,
category IN VARCHAR2 := NULL,
validate IN BOOLEAN  := TRUE)
RETURN VARCHAR2;

--- Drop the SQL Patch syntax:
DBMS_SQLDIAG.DROP_SQL_PATCH (
 name     IN  VARCHAR2,   ignore   IN  BOOLEAN := FALSE);

Show the Syntax of using SQL Patch:

patch_name varchar2(100);
BEGIN
patch_name := sys.dbms_sqldiag.create_sql_patch(
sql_text=>' select employee_id from hr.employees where department_id = :var',         hint_text=>'DYNAMIC_SAMPLING(4)', 
name=>'TEST_SQL_PATCH1');
END;
/

Check the Execution plan of the query using SQL Patch:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'OUTLINE BASIC NOTE'));

OR

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ADVANCED'));

Check the status of the Patch in the system using the view DBA_SQL_PATCHES:

col name for a15
col created for a28
select name, status, created, sql_text from dba_sql_patches where name='TEST_SQL_PATCH1';

Example of using SQL Patch when you want to create for any SQL Query:

-- Create the SQL Patch for Query using Bind Variable:
SQL> DECLARE
  2  patch_name varchar2(100);
  3  BEGIN
  4     patch_name := sys.dbms_sqldiag.create_sql_patch(
  5                   sql_text=>' select employee_id,first_name from hr.employees where department_id = :var',
  6                   hint_text=>'DYNAMIC_SAMPLING(4)',
  7                   name=>'TEST_SQL_PATCH1');
  8  END;
  9  /
PL/SQL procedure successfully completed.

SQL> variable var number
SQL> begin
  2  :var := 100;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select employee_id,first_name from hr.employees where department_id = :var;

EMPLOYEE_ID FIRST_NAME
----------- -------------------
        108 Nancy
        109 Daniel
        110 John
        111 Ismael
        112 Jose Manuel
        113 Luis
6 rows selected

-- Check query is using SQL Patch:
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'OUTLINE BASIC NOTE'));
PLAN_TABLE_OUTPUT

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

EXPLAINED SQL STATEMENT:

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

select employee_id,first_name from hr.employees where department_id = :var

Plan hash value: 235881476
--------------------------------------------------------------
| Id  | Operation                           | Name              |

--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |
|   2 |   INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |
--------------------------------------------------------------

Outline Data
-------------
 /*+
     BEGIN_OUTLINE_DATA
     IGNORE_OPTIM_EMBEDDED_HINTS   OPTIMIZER_FEATURES_ENABLE('18.1.0')
     DB_VERSION('18.1.0')
     ALL_ROWS
     OUTLINE_LEAF(@"SEL$1")
     INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))       BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "EMPLOYEES"@"SEL$1")
      END_OUTLINE_DATA
  */

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

-- Change the value of bind variable
SQL> begin
  2  :var := 101;
  3  end;
  4  /
PL/SQL procedure successfully completed.

SQL> select employee_id,first_name from hr.employees where department_id = :var;
no rows selected

-- Check Query is using SQL Patch with bind variable

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'OUTLINE BASIC NOTE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select employee_id,first_name from hr.employees where department_id = :var

Plan hash value: 235881476
-----------------------------------------------------------------
| Id  | Operation                           | Name              |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |
|   2 |   INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |
--------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "EMPLOYEES"@"SEL$1")
END_OUTLINE_DATA
  */

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


-- Check the SQL Patch exists
SQL> col name for a15
SQL> col created for a28
SQL> select name, status, created, sql_text from dba_sql_patches where name='TEST_SQL_PATCH1';
 NAME            STATUS   CREATED                      SQL_TEXT

--------------- -------- ---------------------------- ----------------------------------------------------------------------------
TEST_SQL_PATCH1 ENABLED  15-MAR-22 05.48.23.000000 PM  select employee_id,first_name from hr.employees where department_id = :var



-- Drop the SQL Patch 
SQL> exec DBMS_SQLDIAG.DROP_SQL_PATCH ('TEST_SQL_PATCH1');

PL/SQL procedure successfully completed.