This post is also available in:
Português (Portuguese (Brazil))
You inherited a query with PARALLEL(16) baked into the code, or the opposite: you need to force parallelism on a query that runs serial, and you can’t touch the application. Vendor code, deploys that strip inline SQL comments, a ticket that takes months to become a release. The SQL Patch exists for exactly this: it injects hints into a specific statement without changing a single line of code.
This post walks through when a SQL Patch is the right tool, how it compares to Baselines and Profiles, and a full 19c lab that forces parallelism on a serial query, with the real output of every step.
What a SQL Patch is (and isn’t)
A SQL Patch is a set of hints the optimizer applies to a statement identified by its text signature. It does not freeze a whole plan: it only adds the hint constraint and lets the CBO pick the best plan within it.
Three things that matter in practice:
- No Tuning Pack required (unlike a SQL Profile).
- It survives application deploys that don’t preserve inline comments.
- It is lightweight: it stores hints, not a full plan with an outline.
When a SQL Patch is the right call
- The application code cannot be changed.
- You already tried a SQL Plan Baseline and it won’t reproduce (
REPRODUCED = NO). - You need something simple: force one specific hint, without pinning the entire plan.
- You want flexibility: the CBO still chooses the plan, but inside the hint’s constraint.
SQL Patch vs Baseline vs Profile vs inline hint
| Approach | What it does | Cost / risk |
|---|---|---|
| SQL Patch | Injects specific hints | Lightweight. Always applies the hint. No Tuning Pack |
| SQL Plan Baseline | Tries to recreate a full plan | Heavy. Can fail to reproduce and bail out |
| SQL Profile | Adjusts estimates (cardinality, cost) | Needs Tuning Pack. Does not guarantee the plan |
| Inline hint | Hint inside the code | Gone on the first deploy that rewrites the SQL |
Bottom line: a Baseline is heavy and may not reproduce; a Profile touches estimates and needs a license; an inline hint dies at deploy time. The Patch is direct, explicit, and persistent.
Lab: force PARALLEL without changing the code
⚠️ Prerequisites: Enterprise Edition (Parallel Query is an EE feature),
parallel_max_servers > 0(the default is fine), and privileges to runDBMS_SQLDIAG. Scenario: Oracle 19c, Single Instance.
1. Test data
A table with no degree of parallelism set (DEGREE = 1) and no hint at all.
DROP TABLE t_lab_px PURGE;
CREATE TABLE t_lab_px AS
SELECT level AS id,
MOD(level, 1000) AS grp,
RPAD('x', 50, 'x') AS pad
FROM dual
CONNECT BY level <= 3000000;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_LAB_PX');
SELECT degree FROM user_tables WHERE table_name = 'T_LAB_PX';
-- DEGREE = 1⚠️ Hit ORA-30009 (Not enough memory for CONNECT BY)? On sessions with a tight PGA, generating 3 million levels at once blows up. Swap the generator for two small
CONNECT BYblocks in a cross join, which never hold everything in the PGA: “sql CREATE TABLE t_lab_px AS WITH gen AS ( SELECT /+ materialize / level AS n FROM dual CONNECT BY level <= 2000 ) SELECT ROWNUM AS id, MOD(ROWNUM, 1000) AS grp, RPAD('x', 50, 'x') AS pad FROM gen a, gen b WHERE ROWNUM <= 3000000;“
2. Baseline: the plan starts serial
SELECT /*+ qb_name(main) */ grp, COUNT(*)
FROM t_lab_px t
GROUP BY grp;
SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE 'SELECT /*+ qb_name(main) */ grp%'
AND sql_text NOT LIKE '%v$sql%';SQL_ID CHILD_NUMBER
------------- ------------
3w40z8nk3h5g5 0SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3w40z8nk3h5g5', NULL, 'BASIC +NOTE'));Plan hash value: 1056803887
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | TABLE ACCESS FULL| T_LAB_PX |
---------------------------------------No parallelism. A plain serial TABLE ACCESS FULL.
💡 The
qb_name(main)is on purpose: naming the query block keeps the patch hint more readable and stable than relying on the auto-generatedSEL$1.
3. Create the SQL Patch injecting PARALLEL
In 19c, CREATE_SQL_PATCH is a function, not a procedure: the return value has to go into a variable.
SET SERVEROUTPUT ON
DECLARE
v_patch VARCHAR2(128);
BEGIN
v_patch := DBMS_SQLDIAG.CREATE_SQL_PATCH(
sql_id => '3w40z8nk3h5g5',
hint_text => 'PARALLEL(4)',
name => 'PX_LAB_PATCH',
description => 'Inject PARALLEL(4) without changing the code',
category => 'DEFAULT',
validate => TRUE);
DBMS_OUTPUT.PUT_LINE('Patch created: ' || v_patch);
END;
/Patch created: PX_LAB_PATCH
PL/SQL procedure successfully completed.SELECT name, status, force_matching, sql_text
FROM dba_sql_patches
WHERE name = 'PX_LAB_PATCH';NAME STATUS FOR SQL_TEXT
-------------- -------- --- -------------------------------------------
PX_LAB_PATCH ENABLED NO SELECT /*+ qb_name(main) */ grp, COUNT(*)...💡 To target a specific table instead of the whole statement, use the object form of the hint:
PARALLEL(t@main 4), in thePARALLEL(alias@query_block degree)format.
4. Run it again and confirm the parallel plan
The sql_id does not change: the text is identical, so only a new child cursor is born.
SELECT /*+ qb_name(main) */ grp, COUNT(*)
FROM t_lab_px t
GROUP BY grp;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3w40z8nk3h5g5', NULL, 'BASIC +NOTE'));Plan hash value: 142943352
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 |
| 3 | HASH GROUP BY | |
| 4 | PX RECEIVE | |
| 5 | PX SEND HASH | :TQ10000 |
| 6 | HASH GROUP BY | |
| 7 | PX BLOCK ITERATOR | |
| 8 | TABLE ACCESS FULL| T_LAB_PX |
---------------------------------------------
Note
-----
- Degree of Parallelism is 4 because of hint
- SQL patch "PX_LAB_PATCH" used for this statementTwo lines in the Note section close the case: the degree of parallelism is now 4 because of the hint, and the patch was actually applied.
5. Confirm runtime usage
SELECT sql_id, child_number, sql_patch
FROM v$sql
WHERE sql_id = '3w40z8nk3h5g5'
AND sql_patch IS NOT NULL;SQL_ID CHILD_NUMBER SQL_PATCH
------------- ------------ ------------
3w40z8nk3h5g5 0 PX_LAB_PATCHDay-to-day administration
-- List
SELECT name, status, force_matching FROM dba_sql_patches;
-- Disable without dropping
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('PX_LAB_PATCH', 'STATUS', 'DISABLED');
-- Re-enable
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('PX_LAB_PATCH', 'STATUS', 'ENABLED');
-- Drop
EXEC DBMS_SQLDIAG.DROP_SQL_PATCH('PX_LAB_PATCH');
-- Confirm usage at runtime
SELECT sql_id, sql_patch FROM v$sql WHERE sql_patch IS NOT NULL;Gotchas worth gold
The patch is tied to the text signature. If the application sends changing literals (a different sql_id on every run), a patch keyed by sql_id matches only that one variant. For the rest, it simply won’t apply.
Force matching does not come through CREATE_SQL_PATCH. The public DBMS_SQLDIAG.CREATE_SQL_PATCH function accepts only sql_id (or sql_text), hint_text, name, description, category, and validate. There is no force_match parameter here. The force_match that does exist belongs to a different function, DBMS_SQLDIAG.ACCEPT_SQL_PATCH, used in the SQL Repair Advisor flow. To force-match a patch created by CREATE_SQL_PATCH, the path is the staging-table workaround (create the staging table with CREATE_STGTAB_SQLPATCH, pack it with PACK_STGTAB_SQLPATCH, edit sqlflags and the signature in the table, then re-import). It is an advanced technique, not a simple parameter.
PARALLEL hints behave their own way. IGNORE_OPTIM_EMBEDDED_HINTS and OPTIMIZER_IGNORE_HINTS do not knock out an embedded PARALLEL. For the reverse case, removing hardcoded parallelism, the path that works is injecting opt_param('optimizer_ignore_parallel_hints' 'true') via a patch (available from 18c onward).
CREATE_SQL_PATCH changed nature in 12.2. If you come from the DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH era, remember: in 19c it is a function with a return value, so capture it in a variable.
Conclusion
When you can’t touch the code but need to control the plan, the script is:
- Identify the
sql_idof the problem query. - Capture the current plan as a baseline (
DBMS_XPLAN.DISPLAY_CURSOR). - Create the patch with the desired hint via
DBMS_SQLDIAG.CREATE_SQL_PATCH(remember: it’s a function in 19c). - Run again and validate through the plan’s
Notesection andv$sql.sql_patch. - If literals vary, evaluate force matching through the staging table, not a parameter.
- Document the patch and keep
ALTERandDROPhandy to roll back.
References
- Oracle Database 19c PL/SQL Packages Reference: DBMS_SQLDIAG. https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQLDIAG.html
- SQLMaria (Nigel Bayliss): How to use a SQL Plan Baseline or a SQL Patch to add Optimizer hints. https://sqlmaria.com/2020/02/25/how-to-use-a-sql-plan-baseline-or-a-sql-patch-to-add-optimizer-hints/
- Jonathan Lewis: sql_patch. https://jonathanlewis.wordpress.com/2019/08/21/sql_patch/
- Jonathan Lewis: force_match patch. https://jonathanlewis.wordpress.com/2023/08/19/force_match-patch/