Neil Chandler's DB Blog

July 2024. Oracle 19c, 23ai

There are 3 main types of SQL Plan Management:

1. SQL Profile
2. SQL Plan Baseline
3. 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.