Re: Optimizer "enhancements" in 19.9?

Does setting the optimizer_features_enabled parameter/hint back to 19.8 revert back to previous behavior?

If yes, you can check what (undocumented) optimizer parameters and fix controls change their state between different optimizer fix control values. I have two scripts (based on v$parameter's underlying x$ tables and v$session_fix_control) that make it easy:

https://tanelpoder.com/posts/scripts-for-drilling-down-into-unknown-optimizer-changes/

You don't need to run this in production - if you have a test/dev env with exactly the same software/patch level installed, can run the scripts there.

--
Thanks,
Tanel Poder
https://tanelpoder.com/events


On Wed, Feb 17, 2021 at 10:59 AM Charles Schultz <sacrophyte_at_gmail.com>
wrote:



> Mohamed, I very much agree with your last statement - how do we find out?


> :)

>

> I tried setting that parameter to false, to no avail (did not help). I

> then tried a number of other similar parameters, and the subquery is still

> being unnested:

>

> _unnest_subquery                    = false

> _distinct_view_unnesting            = false

> _optimizer_unnest_disjunctive_subq  = false

> _optimizer_unnest_corr_set_subq     = false

> _optimizer_unnest_all_subqueries    = false

> _optimizer_unnest_scalar_sq         = false

>

>

> On Wed, Feb 17, 2021 at 9:43 AM Mohamed Houri <mohamed.houri_at_gmail.com>

> wrote:

>

>> It looks like, in the 19.9 version, Oracle has refused to *unnest* the

>> subquery: *IN (select max(to_date(to_char(a.lastmoddat)*

>>

>>

>>

>> Indeed, the FILTER predicate which appears in the unnested version of

>> your second execution plan acts as a NESTED LOOPS would do which has

>> certainly degraded the performance of your query.

>>

>>

>>

>> If this is a COST BASED canceled transformation then hinting your query

>> like below will work around the problem

>>

>>

>>

>> SELECT

>>

>>     e1.ename,

>>

>>     e1.sal,

>>

>>     e1.deptno

>>

>> FROM

>>

>>     emp e1

>>

>> WHERE

>>

>>     sal = (

>>

>>         SELECT /*+ opt_param('_unnest_subquery' 'false') */

>>

>>             MAX(e2.sal)

>>

>>         FROM

>>

>>             emp e2

>>

>>         WHERE

>>

>>             e2.deptno = e1.deptno

>>

>>     );

>>

>>

>>

>> But,  if 19.9 introduces a new limitation to the subquery unnesting

>> transformation then we need to know what this limitation is?

>>

>>

>>

>> Best regards

>>

>> Mohamed

>>

>> Le mer. 17 févr. 2021 à 15:02, Charles Schultz <sacrophyte_at_gmail.com> a

>> écrit :

>>

>>> Good day,

>>>

>>> tl&dr - what changed in 19.9 to cause the optimizer to 1) favor SORT

>>> AGGREGATE over HASH JOIN, and 2) profiles generated under 19.8 fail basic

>>> validity checks under 19.9?

>>>

>>> We have a query that performs well under 19.8, but horrible under 19.9.

>>> After isolating the issue to be a result of the October 2020 RU, I tried to

>>> compare 10053 trace event outputs (and was further surprised by how the

>>> formatting of the 10053 trace has changed a little bit), and I found that

>>> 19.8 is favoring a HASH JOIN, while 19.9 was favoring the nested-loop-like

>>> SORT AGGREGATE. I have not yet figured out the new jargon used in 19.9 to

>>> be able to determine exactly why the optimizer chose SORT AGGREGATE over a

>>> HASH JOIN - still working on that.

>>>

>>> Another fascinating item I have observed is that the outline/profile

>>> generated with coe_xfr_sql_profile under 19.8 is actually invalid under

>>> 19.9 - the 19.9 10053 trace shows:

>>>   Hint Report:

>>>     Query Block: SEL$2

>>>       Unused: UNNEST(_at_"SEL$2")

>>>         reason: Failed basic validity checks

>>>

>>> We are testing with two Enterprise Edition databases, one still at 19.8,

>>> the other patched to 19.9 this morning (Wednesday, Feb 17).

>>>

>>> The query is (I didn't write it, and I have already shown that a rewrite

>>> with CTE works around the problem):

>>> SELECT NOTES.NOTETEXT as NOTETEXT, NOTES.ENTITYID as ENTITYID,

>>> NOTES.ENTITYSUBTYPEID as ENTITYSUBTYPEID FROM NOTES WHERE

>>> to_date(to_char(NOTES.LASTMODDATE,'yyyymmdd') || to_char

>>> (NOTES.LASTMODTIME,'hh24miss'),'yyyymmddhh24miss')

>>> IN (SELECT MAX(to_date(to_char(A.LASTMODDATE,'yyyymmdd') || to_char

>>> (A.LASTMODTIME,'hh24miss'),'yyyymmddhh24miss'))

>>>       FROM NOTES A

>>>      WHERE A.ENTITYSUBTYPEID=NOTES.ENTITYSUBTYPEID

>>>       AND A.ENTITYID=NOTES.ENTITYID)

>>> /

>>>

>>> The query plan in 19.8:

>>>

>>> ----------------------------------------+-----------------------------------+

>>> | Id  | Operation             | Name    | Rows  | Bytes | Cost  | Time

>>>    |

>>>

>>> ----------------------------------------+-----------------------------------+

>>> | 0   | SELECT STATEMENT      |         |       |       |   12K |

>>>     |

>>> | 1   |  HASH JOIN            |         |   621 |  458K |   12K |

>>>  00:02:27 |

>>> | 2   |   VIEW                | VW_SQ_1 |  329K |   17M |   11K |

>>>  00:02:12 |

>>> | 3   |    HASH GROUP BY      |         |  329K |   11M |   11K |

>>>  00:02:12 |

>>> | 4   |     TABLE ACCESS FULL | NOTES   |  374K |   13M |  7720 |

>>>  00:02:33 |

>>> | 5   |   TABLE ACCESS FULL   | NOTES   |  4889 | 1681K |   100 |

>>>  00:00:02 |

>>>

>>> ----------------------------------------+-----------------------------------+

>>>

>>> The query plan in 19.9:

>>>

>>> ---------------------------------------+-----------------------------------+

>>> | Id  | Operation            | Name    | Rows  | Bytes | Cost  | Time

>>>    |

>>>

>>> ---------------------------------------+-----------------------------------+

>>> | 0   | SELECT STATEMENT     |         |       |       | 2814M |

>>>   |

>>> | 1   |  FILTER              |         |       |       |       |

>>>   |

>>> | 2   |   TABLE ACCESS FULL  | NOTES   |  377K |  130M |  7738 |

>>>  00:02:33 |

>>> | 3   |   SORT AGGREGATE     |         |     1 |    35 |       |

>>>   |

>>> | 4   |    TABLE ACCESS FULL | NOTES   |     1 |    35 |  7719 |

>>>  00:02:33 |

>>>

>>> ---------------------------------------+-----------------------------------+

>>>

>>> Bottom line question for you all - what changed in 19.9 to cause this?

>>>

>>> --

>>> Charles Schultz

>>>

>>

>>

>> --

>>

>> Houri Mohamed

>>

>> Oracle DBA-Developer-Performance & Tuning

>>

>> Visit My         - Blog <http://www.hourim.wordpress.com/>

>>

>> Let's Connect -  <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin

>> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

>>

>> My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri

>> <https://twitter.com/MohamedHouri>

>>

>>

>

> --

> Charles Schultz

>


--
http://www.freelists.org/webpage/oracle-l