Re: Optimizer "enhancements" in 19.9?

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>

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