1. What is an Oracle SQL Patch?

An Oracle SQL Patch is a mechanism that allows DBAs to inject optimizer hints into a SQL statement without changing application code.

It is mainly used when:

• A SQL statement suddenly performs badly
• You cannot modify the SQL in the application
• You want a safe, reversible tuning fix

Unlike SQL Profiles or SQL Plan Baselines, SQL Patches are lighter and more targeted.

2. Why SQL Patch is Used

Problems SQL Patch Solves

• Bad execution plans after statistics refresh
• Wrong join methods (HASH JOIN instead of NESTED LOOP)
• Missing index usage
• Performance regression after upgrade or patching

Why Not Change SQL Code?

• Application owned by vendor
• Code deployed in production and change is risky
• Same SQL used at multiple places

✅ SQL Patch fixes performance without touching code

3. SQL Patch vs Other Tuning Options

4. Important Prerequisites

Before creating a SQL Patch:

1. SQL text must be exactly the same
2. SQL should be parseable
3. Required indexes must already exist
4. User must have privileges:

GRANT ADMINISTER SQL MANAGEMENT OBJECT TO <user>;

5. How SQL Patch Works (Internals)

• Oracle matches the SQL text
• Injects hints at parse time
• Optimizer generates a new execution plan
• No plan is forced — optimizer still decides

6. SQL Patch Creation Methods by Version

Oracle 12.1

Uses internal package

SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH

Oracle 12.2 and Above

Uses public supported package

SYS.DBMS_SQLDIAG.CREATE_SQL_PATCH

7. Step-by-Step SQL Patch Creation (Oracle 12.1)

Step 1: Identify Problem SQL

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

Step 2: Decide Required Hint

Example: Force index usage

INDEX(employees emp_idx1)

Step 3: Create SQL Patch

BEGIN
  SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(
    sql_text    => 'SELECT * FROM employees WHERE department_id = 10',
    hint_text   => 'INDEX(employees emp_idx1)',
    name        => 'EMP_DEPT_INDEX_PATCH',
    description => 'Force index usage for employee query'
  );
END;
/

Step 4: Flush Shared Pool (Optional)

ALTER SYSTEM FLUSH SHARED_POOL;

8. Step-by-Step SQL Patch Creation (Oracle 12.2+)

Step 1: Identify SQL

SELECT sql_id, sql_text
FROM v$sql
WHERE sql_id = 'abc123xyz';

Step 2: Create SQL Patch

BEGIN
  DBMS_SQLDIAG.CREATE_SQL_PATCH(
    sql_text  => 'SELECT * FROM employees WHERE department_id = 10',
    hint_text => 'INDEX(employees emp_idx1)',
    name      => 'EMP_DEPT_INDEX_PATCH'
  );
END;
/

9. Example: Fixing a Slow Query

Problem Query

SELECT *
FROM orders
WHERE customer_id = 100;

Issue

• Full table scan on ORDERS
• ORDERS table is large
• Index exists: ORDERS_CUST_IDX

Solution: SQL Patch

BEGIN
  DBMS_SQLDIAG.CREATE_SQL_PATCH(
    sql_text  => 'SELECT * FROM orders WHERE customer_id = 100',
    hint_text => 'INDEX(orders ORDERS_CUST_IDX)',
    name      => 'ORDERS_CUST_PATCH'
  );
END;
/

Result

• Optimizer now uses index
• Query response time improves significantly
• No application change needed

10. Verify SQL Patch

List SQL Patches

SELECT name, status, description
FROM dba_sql_patches;

Check Applied Hints

SELECT *
FROM v$sql
WHERE sql_id = '<sql_id>';

Look for hint usage in execution plan.

11. Disable or Drop SQL Patch (Rollback)

Disable Patch

EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH(
  name => 'ORDERS_CUST_PATCH',
  attribute_name => 'STATUS',
  value => 'DISABLED'
);

Drop Patch

EXEC DBMS_SQLDIAG.DROP_SQL_PATCH('ORDERS_CUST_PATCH');

When NOT to Use SQL Patch

❌ If SQL text changes frequently
❌ If plan stability is required → use SQL Baseline
❌ If problem is missing statistics → gather stats first