Re: Optimizer "enhancements" in 19.9?

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

> 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