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@xxxxxxxxx> 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(@"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
