One of the small changes (and, potentially big but temporary, threats) in 18.3 is the status of the “ignore hints” parameter. It ceases to be a hidden (underscore) parameter so you can now officially set parameter optimizer_ignore_hints to true in the parameter file, or at the system level, or at the session level. The threat, of course, it that some of your code may use the hidden version of the parameter (perhaps in an SQL_Patch as an opt_param() option rather than in its hint form) which no longer works after the upgrade.
But there’s more. The parameter (whether the old hidden version or the new revealed version) doesn’t make the optimizer ignore parallel() hints. But 18.3 now has a related parameter optimizer_ignore_parallel_hints to address this limitation. Here’s a quick demo – we start by creating a table and then running a query where the full tablescan is clearly the default strategy that the optimizer would take if we didn’t hint an indexed access path:
rem rem Script: ignore_parallel_hints.sql rem Author: Jonathan Lewis rem Dated: May 2019 rem create table t1 as with generator as ( select rownum id from dual connect by level <= 1e4 -- > comment to avoid WordPress format issue ) select rownum id, mod(rownum,10) n1, lpad(rownum,10,'0') v1, lpad('x',100,'x') padding from generator v1, generator v2 where rownum <= 1e4 -- > comment to avoid WordPress format issue ; create index t1_i1 on t1(id); set serveroutput off prompt ============= prompt Baseline test prompt ============= select /*+ index(t1) */ n1, sum(id) from t1 where id > 0 group by n1 order by n1 ; select * from table(dbms_xplan.display_cursor); SQL_ID gudnnk7j7q5bz, child number 0 ------------------------------------- select /*+ index(t1) */ n1, sum(id) from t1 where id > 0 group by n1 order by n1 Plan hash value: 356059923 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 198 (100)| | | 1 | SORT GROUP BY | | 10 | 70 | 198 (2)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 10000 | 70000 | 196 (1)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T1_I1 | 10000 | | 22 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID">0)
Now we repeat the exercise with the version-specific “alter session” command below – and you should try each option with each version of Oracle if you want to do the complete test cycle – to see that the session will ignore hints and the plan will change (side note – using the underscore version with 18.3 doesn’t raise an error, the statement is silently ignored):
alter session set "_optimizer_ignore_hints" = true; alter session set "optimizer_ignore_hints" = true; SQL_ID gudnnk7j7q5bz, child number 1 ------------------------------------- select /*+ index(t1) */ n1, sum(id) from t1 where id > 0 group by n1 order by n1 Plan hash value: 3946799371 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 27 (100)| | | 1 | SORT GROUP BY | | 10 | 70 | 27 (12)| 00:00:01 | |* 2 | TABLE ACCESS FULL| T1 | 10000 | 70000 | 25 (4)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID">0)
Then, of course, we have to test a query with a parallel() hint – or shared() hint, which is the internal equivalent you will notice occasionally in outlines or the “remote” statement for distributed execution plans – to show that we don’t yet ignore parallel queries – the plans following the code are from 18.3:
select /*+ parallel(t1 3) */ n1, sum(id) from t1 where id > 0 group by n1 order by n1 ; select * from table(dbms_xplan.display_cursor); alter session set "optimizer_ignore_parallel_hints" = true; select /*+ parallel(t1 3) */ n1, sum(id) from t1 where id > 0 group by n1 order by n1 ; select * from table(dbms_xplan.display_cursor); SQL_ID 7jynurdtc48kv, child number 0 ------------------------------------- select /*+ parallel(t1 3) */ n1, sum(id) from t1 where id > 0 group by n1 order by n1 Plan hash value: 2919148568 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 10 (100)| | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | 10 | 70 | 10 (10)| 00:00:01 | Q1,01 | P->S | QC (ORDER) | | 3 | SORT GROUP BY | | 10 | 70 | 10 (10)| 00:00:01 | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 10 | 70 | 10 (10)| 00:00:01 | Q1,01 | PCWP | | | 5 | PX SEND RANGE | :TQ10000 | 10 | 70 | 10 (10)| 00:00:01 | Q1,00 | P->P | RANGE | | 6 | HASH GROUP BY | | 10 | 70 | 10 (10)| 00:00:01 | Q1,00 | PCWP | | | 7 | PX BLOCK ITERATOR | | 10000 | 70000 | 9 (0)| 00:00:01 | Q1,00 | PCWC | | |* 8 | TABLE ACCESS FULL| T1 | 10000 | 70000 | 9 (0)| 00:00:01 | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 8 - access(:Z>=:Z AND :Z<=:Z) -- > comment added to avoid wordpress format issue filter("ID">0) Note ----- - Degree of Parallelism is 3 because of table property Session altered. SQL_ID 7jynurdtc48kv, child number 1 ------------------------------------- select /*+ parallel(t1 3) */ n1, sum(id) from t1 where id > 0 group by n1 order by n1 Plan hash value: 3946799371 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 27 (100)| | | 1 | SORT GROUP BY | | 10 | 70 | 27 (12)| 00:00:01 | |* 2 | TABLE ACCESS FULL| T1 | 10000 | 70000 | 25 (4)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID">0)
tl;dr
In 18.3 Oracle exposes the parameter optimizer_ignore_hints – any code using depending on the hidden version of this parameter will no longer behave as expected. 18.3 also introduces optimiser_ignore_parallel_hints to allow you to ignore parallel hints as well.
I’d call this a bug. Even though documentation doesn’t explicitly state this, I assume “parallel hints” is a subset of “hints”, so optimizer_ignore_hints should ignore every-freakin’-thing including parallel hints. Curious to hear CBO team explain their reasoning of current behavio(u)r.
Comment by Timur Akhmadeev — June 3, 2019 @ 1:29 pm BST Jun 3,2019 |
Timur,
Thanks for the comment.
There is the argument that “_optimizer_ignore_hints” is undocumented so no-one should be using it without direct instructions from Oracle Support, so it doesn’t matter what anyone thinks it “should” do, it is what it is.
As for the reasoning – it’s worth noting that the “parallel()” hint doesn’t get into SQL Baselines, so when a baseline includes the hint /*+ IGNORE_OPTIM_EMBEDDED_HINTS */ it’s (presumably) supposed to allow the degree of parallelism that the original code or table definitions specified, so perhaps the code to handle the parameter is the same code that handles the internal hint.
Comment by Jonathan Lewis — June 3, 2019 @ 2:21 pm BST Jun 3,2019 |
will this hint be also ignored that is used during stats gathering?
SELECT /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */
Comment by Anonymous — February 26, 2020 @ 9:14 pm GMT Feb 26,2020 |
by the way, we are still on 12.1
Comment by Anonymous — February 26, 2020 @ 9:22 pm GMT Feb 26,2020 |
I think I’ve written a note somewhere (though I can’t find it at present) where I showed a few examples where the internally generated SQL (like the code used for gathering stats) wasn’t affected by setting the parameter (at least, that was in an earlier version when the parameter was hidden). I doubt that I’ve tested every possible variation of generated code, though, so it’s probably best to do a few tests of any that you think are important – and repeat the tests each time you upgrade or patch.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — February 26, 2020 @ 11:27 pm GMT Feb 26,2020 |