Optimizer Regression After Upgrade? Fix Slow Queries Using DBMS_SQLDIAG.CREATE_SQL_PATCH

 

Intro

We are in the era of AI, where increasing processing power is critical to managing rapidly growing and complex workloads. As enterprise systems continue to scale, database performance challenges have become increasingly common, especially under high-throughput operations. 

From my experience, Oracle Database stands out as the most flexible and robust platform for identifying, analyzing, and resolving performance issues, thanks to its advanced tooling and diagnostic capabilities.

Recently, we encountered a performance bottleneck in an Exadata Cloud@Customer (ExaCS) environment, where the database was processing a high volume of insert operations originating from a Kafka data stream.

In this case, the application had been running on Oracle 11g for more than eight years without major issues. However, after migrating to a newer database version, we faced multiple performance hiccups. While upgrades are usually straightforward, the optimizer behavior changes introduced in the newer version made performance tuning trickier and more time-consuming. Handling execution plan shifts and adapting SQL performance required careful analysis and fine-tuning to stabilize the environment.

It’s not easy to introduce changes directly on a long-running production database, especially one supporting critical business operations. In this example, most queries performed well under the 19c optimizer, but we encountered one major query that experienced significant slowness and failed to complete within the expected time window. Troubleshooting required deep investigation, plan comparisons, and precise tuning to restore performance without disrupting stable workloads.

In this blog, I will demonstrate how we resolved performance issues for specific queries without making any changes to the application code. The DBMS_SQLDIAG.CREATE_SQL_PATCH procedure provides an effective way to apply optimizer hints and fix problematic SQL statements without modifying the original SQL text.

For more details, you can also refer to Oracle’s documentation:
How to Create a SQL Patch to Add Hints to Application SQL Statements (Doc ID 1931944.1).

Why We Needed SQL Patches.

In our case, the application had been running on Oracle 11g for more than eight years.
After upgrading to Oracle 19c, most queries performed better with the new optimizer, but a few critical queries started experiencing severe performance issues.

Introducing changes directly into a long-running production database is not easy.
For one problematic query, switching the optimizer behavior back to 11.2.0.4 resolved the issue, and we achieved this without modifying the application code — using a SQL Patch.

Sample SQL Patch Creation.

Here’s an example of how to create a SQL Patch using the DBMS_SQLDIAG.CREATE_SQL_PATCH procedure:

DECLARE
  v_patch_name VARCHAR2(30);
BEGIN
  v_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH (
    sql_id       => 'your_sql_id',
    hint_text    => '/*+ USE_HASH_JOIN */',
    name         => 'patch_use_hash',
    description  => 'Forcing hash join to improve performance'
  );
END;
/
  

Key Parameters

Parameter	Description

sql_id

The SQL_ID from AWR/SQL Monitor of the problematic query

hint_text

The optimizer hints you want to inject

name	A custom name for your SQL patch

description

A note to describe the purpose

Example: Forcing 11.2.0.4 Optimizer for a Specific SQL.

In our case, we applied the 11.2.0.4 optimizer behavior to a specific SQL by using the following:

VARIABLE x VARCHAR2(100);
EXEC :x := DBMS_SQLDIAG.CREATE_SQL_PATCH(
  sql_id      => 'grnbsvudp26j3',
  hint_text   => 'optimizer_features_enable(''11.2.0.4'')',
  name        => 'SQL_Patch_grnbsvudp26j3'
);
    

How to Validate If the SQL Is Using the New Optimizer

You can confirm that the SQL Patch has been applied using:

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

Sample Execution Plan Output

SQL> perf_dplan_cursor_outline.sql
Enter value for SQL_ID : grnbsvudp26j3
Enter Child Number (Default is 0) :

+------------------------------------------------------------------------+
| Report   : Execution Plan for SQL_ID in Cursor Cache                   |
| Instance : EXADB1                                                     |
| SQL_ID   : grnbsvudp26j3                         |
+------------------------------------------------------------------------+

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------

Plan hash value: 2610349306

------------------------------------------------------------------------
| Id  | Operation                            | Name                    |
------------------------------------------------------------------------
|   0 | INSERT STATEMENT                     |                         |
|   1 |  LOAD TABLE CONVENTIONAL             | TEST_ASSET_ALERT_HISTORY |
|   2 |   CONCATENATION                      |                         |
|   3 |    NESTED LOOPS                      |                         |
|   4 |     NESTED LOOPS                     |                         |
|   5 |      TABLE ACCESS BY INDEX ROWID     | TEST_ASSET_WORK          |
|   6 |       INDEX RANGE SCAN               | IDX_JAW_JID_CID_JEID    |
|   7 |      PARTITION RANGE ITERATOR        |                         |
|   8 |       INDEX RANGE SCAN               | IDX_DGH_CAID_ET_FID_TT  |
|   9 |     TABLE ACCESS BY LOCAL INDEX ROWID| TEST_HISTORY        |
|  10 |    NESTED LOOPS                      |                         |
|  11 |     NESTED LOOPS                     |                         |
|  12 |      TABLE ACCESS BY INDEX ROWID     | TEST_ASSET_WORK          |
|  13 |       INDEX RANGE SCAN               | IDX_JAW_JID_CID_JEID    |
|  14 |      PARTITION RANGE ITERATOR        |                         |
|  15 |       INDEX RANGE SCAN               | IDX_DGH_CAID_ET_FID_TT  |
|  16 |     TABLE ACCESS BY LOCAL INDEX ROWID| TEST_HISTORY        |
------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      MERGE(@"SEL$1" >"SEL$2")
      OUTLINE_LEAF(@"INS$1")
      OUTLINE_LEAF(@"SEL$58A6D7F6_1")
      USE_CONCAT(@"SEL$58A6D7F6" 8 OR_PREDICATES(8) PREDICATE_REORDERS((16 4)
              (8 6) (9 7) (10 8) (11 9) (12 10) (13 11) (14 12) (15 13) (4 14) (6 15)
              (7 16)))
      OUTLINE_LEAF(@"SEL$58A6D7F6_2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      FULL(@"INS$1" "TEST_ASSET_ALERT_HISTORY"@"INS$1")
      INDEX_RS_ASC(@"SEL$58A6D7F6_1" "A"@"SEL$1" ("TEST_ASSET_WORK"."JOB_ID"
              "JOB_ASSET_WORK"."COMPANY_ID" "JOB_ASSET_WORK"."JOB_EVENT_ID"))
      INDEX(@"SEL$58A6D7F6_1" "DH"@"SEL$1"
              ("TEST_HISTORY"."COMPANY_ASSET_ID" "TEST_HISTORY"."EVENT_TIME"
              "TEST_HISTORY"."FACILITY_ID" "TEST_HISTORY"."TRANSACTION_TYPE"))
      INDEX_RS_ASC(@"SEL$58A6D7F6_2" "A"@"SEL$58A6D7F6_2"
              ("TEST_ASSET_WORK"."JOB_ID" "JOB_ASSET_WORK"."COMPANY_ID"
              "TEST_ASSET_WORK"."JOB_EVENT_ID"))
      INDEX(@"SEL$58A6D7F6_2" "DH"@"SEL$58A6D7F6_2"
              ("TEST_HISTORY"."COMPANY_ASSET_ID" "TEST_HISTORY"."EVENT_TIME"
              "TEST_HISTORY"."FACILITY_ID" "TEST_HISTORY"."TRANSACTION_TYPE"))
      LEADING(@"SEL$58A6D7F6_1" "A"@"SEL$1" "DH"@"SEL$1")
      LEADING(@"SEL$58A6D7F6_2" "A"@"SEL$58A6D7F6_2" "DH"@"SEL$58A6D7F6_2")
      USE_NL(@"SEL$58A6D7F6_1" "DH"@"SEL$1")
      NLJ_BATCHING(@"SEL$58A6D7F6_1" "DH"@"SEL$1")
      USE_NL(@"SEL$58A6D7F6_2" "DH"@"SEL$58A6D7F6_2")
      NLJ_BATCHING(@"SEL$58A6D7F6_2" "DH"@"SEL$58A6D7F6_2")
      END_OUTLINE_DATA
  */

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


87 rows selected.

SQL>

Key Highlights from the Execution Plan: Optimizer hint applied successfully:

Summary

• Problem: After upgrading to 19c, a few queries slowed down due to optimizer changes.

• Solution: Created a SQL Patch to apply legacy optimizer features.

• Advantage: No changes were made to the application code.

• Result: Query performance was restored to expected levels.

Conclusion

Handling performance issues in long-running Oracle databases, especially after major version upgrades, can be challenging. Optimizer behavior often changes between versions, and while most queries benefit from the enhancements, certain critical queries may experience unexpected regressions.

In such scenarios, DBMS_SQLDIAG.CREATE_SQL_PATCH provides a powerful, non-intrusive solution to fix problematic queries without making any application changes. By injecting optimizer hints or enforcing a specific optimizer version, we can stabilize performance quickly and ensure business continuity.

This approach not only reduces risk but also saves significant time during troubleshooting, particularly in production environments where application changes may involve lengthy testing cycles. SQL patches act as a bridge between performance optimization and application stability, making them an essential tool for every Oracle DBA.