Optimizer "enhancements" in 19.9?
Date: Wed, 17 Feb 2021 08:02:23 -0600
Message-ID: <CAPZQniVLwqZbBLeVs8L6HWXs6cM6H7sX6XJ5FvRcyKiPC2=+xg_at_mail.gmail.com>
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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 17 2021 - 15:02:23 CET