Intro
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).
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
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'OUTLINE BASIC NOTE'));
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>
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.
