July 2024. Oracle 19c, 23ai
There are 3 main types of SQL Plan Management:
- SQL Profile
- SQL Plan Baseline
- SQL Patch
So first some quick background info about the 3 options and then I’ll tell you about my mistake.
SQL Profile
A SQL Profile is a collections of hints to be applied to a SQL statement. It needs a Tuning Pack license! and does not “guarantee” the plan you are hoping to get- but it will usually reproduce the plan. The hints are always applied to the SQL. As a bonus, if you are passing literals to the same statement (so you get different SQL ID’s), you can “force match” the SQL to replace the literals with dummy binds to try to get the hints applied to the SQL statement
SQL Plan Baseline
A SQL Plan Baselines is also a collection of hints but unlike a SQL Profile it attempts to recreate the same SQL Execution Plan. It does this by comparing the produced plans and and running the desired plan if it matches. There is also a parse overhead, as the original statement is also parsed without the hints and retained (but is not used until accepted) if it seems to be better (i.e. lower cost) than accepted plan(s). It is possible to evolve SQL Plan Baselines to accept new “better” plans, and you can allow a several plans to be acceptable for the SQL Statement. It only works against specific SQL ID’s, and if it fails to recreate the expected Execution Plan, none of the hints from that plan are applied and it parses as normal.
SQL Patch
This is a little different. A SQL Patch allows you to inject your own hints into a SQL Statement without changing the statement. It can be a good way to persuade the optimizer to get the execution plan you want.
It makes a lot of sense to use a SQL_PATCH to inject hints rather than change the code.
When you upgrade and want to test new optimizer features, you can just disable and re-enable the SQL_PATCHES
to run your testing, rather than go through the hassle of changing all of the application code
and then changing it back again if it doesn’t improve.
One case I has recently was where the code has a PARALLEL(16) hint embedded. To change the code would mean going back to the supplier, persuading them to remove the hint, taking a code release, fully testing it and releasing it. Here’s an example showing what happened:
SELECT /*+ PARALLEL(16) NO_MERGE USE_HASH (ob,ow) */ ob.object_type,count(*) FROM t_obj ob WHERE ob.object_type like:"SYS_B_0" AND ob.owner in (SELECT ow.owner FROM t_own ow WHERE ow.flat = :"SYS_B_1") GROUP BY object_type; Plan hash value: 930377026 ------------------------------------------------- | 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 | HASH JOIN RIGHT SEMI | | | 8 | TABLE ACCESS FULL | T_OWN | | 9 | PX BLOCK ITERATOR | | | 10 | INDEX FAST FULL SCAN| T_OBJ_I1 | ------------------------------------------------- Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 4 (U - Unused (1), N - Unresolved (1)) --------------------------------------------------------------------------- 0 - STATEMENT - PARALLEL(16) 1 - SEL$5DA710D3 N - USE_HASH (ob,ow) - NO_MERGE 10 - SEL$5DA710D3 / OB@SEL$1 U - USE_HASH (ob,ow)
We can see from the hint report that it’s using PARALLEL.
The other hints are there just to demonstrate hint types in this example.
So, lets stop the PARALLEL hint by telling it to ignore embedded hints (ignore_optim_embedded_hints) with a SQL patch for this SQL ID:
declare x varchar2(100); begin x := dbms_sqldiag.create_sql_patch( sql_id => '30fdw3fv86zva' , hint_text=> q'[ignore_optim_embedded_hints]' , description => q'[ignore_optim_embedded_hints]' , name => 'SQL_PATCH_HINTS'); end; / PL/SQL procedure successfully completed. select name,status,description,sql_text from dba_sql_patches; NAME STATUS DESCRIPTION SQL_TEXT --------------- ---------- --------------------------- -------------------------------------------------- SQL_PATCH_HINTS ENABLED ignore_optim_embedded_hints SELECT /*+ PARALLEL(16) NO_MERGE USE_HASH (ob,ow) */
Run the SQL again and check the plan
select * from table(dbms_xplan.display_cursor('30fdw3fv86zva',null,'BASIC +HINT_REPORT +NOTE')); ------------------------------------------------- | 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 | HASH JOIN RIGHT SEMI | | | 8 | TABLE ACCESS FULL | T_OWN | | 9 | PX BLOCK ITERATOR | | | 10 | INDEX FAST FULL SCAN| T_OBJ_I1 | ------------------------------------------------- Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 5 (U - Unused (1), N - Unresolved (1)) --------------------------------------------------------------------------- 0 - STATEMENT - PARALLEL(16) - ignore_optim_embedded_hints 1 - SEL$5DA710D3 N - USE_HASH (ob,ow) - NO_MERGE 10 - SEL$5DA710D3 / OB@SEL$1 U - USE_HASH (ob,ow) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS Note ----- - SQL patch "SQL_PATCH_TEST1" used for this statement
Ah! Problem. We can see IGNORE_OPTIM_EMBEDDED_HINTS is now in the hint list, and it has stopped the USE_HASH hint but not the others. WHY NOT?
This is because I made a MISTAKE and had forgotten that there are different categories of hints. IGNORE_OPTIM_EMBEDDED_HINTS only works on some hints (e.g. USE_HASH, INDEX, etc).
NO_MERGE is not an optimizer hint, apparently (aside: it is really, as my next post shows, but just wasn’t disabled here).
Hints such as APPEND are behavioural and not affected by IGNORE_OPTIM_EMBEDDED_HINTS, and PARALLEL hints are in a class all of their own…
Fortunately, from Oracle 18C there’s some new optimizer parameters which can help us:
optimizer_ignore_parallel_hints
show parameter optimizer_ignore NAME TYPE VALUE ------------------------------------ --------- ----- optimizer_ignore_hints boolean FALSE optimizer_ignore_parallel_hints boolean FALSE
This is an initialisation parameter (e.g. alter session set optimizer_ignore_parallel_hints=true) but we can work around that by using the OPT_PARAM hint:
declare x varchar2(100); begin x := dbms_sqldiag.create_sql_patch( sql_id => '30fdw3fv86zva' , hint_text => q'[opt_param('optimizer_ignore_parallel_hints' 'true')]' , description => q'[opt_param('optimizer_ignore_parallel_hints' 'true')]' , name => 'SQL_PATCH_TEST1'); end; / PL/SQL procedure successfully completed. select name,status,description,sql_text from dba_sql_patches; NAME STATUS DESCRIPTION SQL_TEXT --------------- ---------- -------------------------------------------------- ---------------------------- SQL_PATCH_TEST1 ENABLED opt_param('optimizer_ignore_parallel_hints' 'true') SELECT /*+ PARALLEL(16) .....
If we then run the SQL and check the plan – it’s serial! – so we can see we have cancelled the PARALLEL hint!
select * from table(dbms_xplan.display_cursor('30fdw3fv86zva',null,'BASIC +HINT_REPORT +NOTE')); SELECT /*+ PARALLEL(16) NO_MERGE USE_HASH (ob,ow) */ob.object_type,count(*) FROM t_obj ob WHERE ob.object_type like:"SYS_B_0" AND ob.owner in (SELECT ow.owner FROM t_own ow WHEREow.flat = :"SYS_B_1") GROUP BY object_type ------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | | 2 | HASH JOIN RIGHT SEMI | | | 3 | TABLE ACCESS FULL | T_OWN | | 4 | INDEX FAST FULL SCAN| T_OBJ_I1 | ------------------------------------------- Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 5 (U - Unused (2), N - Unresolved (1)) --------------------------------------------------------------------------- 0 - STATEMENT U - PARALLEL(16) / because of _optimizer_ignore_parallel_hints - opt_param('optimizer_ignore_parallel_hints' 'true') 1 - SEL$5DA710D3 N - USE_HASH (ob,ow) - NO_MERGE 4 - SEL$5DA710D3 / OB@SEL$1 U - USE_HASH (ob,ow) Note ----- - SQL patch "SQL_PATCH_TEST1" used for this statement
But, our other hints are still enabled. Just what we wanted.
optimizer_ignore_hints
So what happens if we use optimizer_ignore_hints in a SQL Patch?
Well, the patch is used but it doesn’t work. The HINT_REPORT says the syntax is in error.
It would seem that optimizer_ignore_hints is not allowed within an OPT_PARAM construct.
I should have worked out why this happened but didn’t, so after a chat with Nigel Bayliss, the Oracle Optimizer PM, he pointed out that optimizer_ignore_hints tells Oracle to ignore all hints including the OPT_PARAM hint, meaning the hint effectively disables itself. I think that maybe a syntax error is a good thing here as depending upon when it disables itself (i.e. which hints get disabled in what order), it may have disabled some but not all other hints leading to inconsistent results.
optimizer_ignore_hints effectively disabling itself: declare x varchar2(100); begin x := dbms_sqldiag.create_sql_patch( sql_id => '30fdw3fv86zva' , hint_text=> q'[opt_param('optimizer_ignore_hints' 'true')]' , description=> q'[opt_param('optimizer_ignore_hints' 'true')]' , name => 'SQL_PATCH_TEST1'); end; / select name,status,description,sql_text from dba_sql_patches; NAME STATUS DESCRIPTION SQL_TEXT ----------------- ---------- ------------------------------------------ --------------------------------------------- SQL_PATCH_TEST1 ENABLED opt_param('optimizer_ignore_hints' 'true') SELECT /*+ PARALLEL(4) NO_MERGE USE_HASH.... SELECT /*+ PARALLEL(16) NO_MERGE USE_HASH (ob,ow) */ ob.object_type,count(*) FROM t_obj ob WHERE ob.object_type like :"SYS_B_0" AND ob.owner in (SELECT ow.owner FROM t_own ow WHERE ow.flat = :"SYS_B_1") GROUP BY object_type Plan hash value: 930377026 ------------------------------------------------- | 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 | HASH JOIN RIGHT SEMI | | | 8 | TABLE ACCESS FULL | T_OWN | | 9 | PX BLOCK ITERATOR | | | 10 | INDEX FAST FULL SCAN| T_OBJ_I1 | ------------------------------------------------- Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 5 (U - Unused (1), N - Unresolved (1), E - Syntax error (1)) --------------------------------------------------------------------------------------- 0 - STATEMENT - PARALLEL(4) 0 - SEL$1 E - opt_param 1 - SEL$5DA710D3 N - USE_HASH (ob,ow) - NO_MERGE 10 - SEL$5DA710D3 / OB@SEL$1 U - USE_HASH (ob,ow) Note ----- - Degree of Parallelism is 16 because of hint - SQL patch "SQL_PATCH_TEST1" used for this statement
ignore_optim_embedded_hints gets around this by not being classified as an Optimizer hint, so it doesn’t disable itself.
Neil Chandler's DB Blog 
Leave a reply to Blog Cancel reply