Re: Optimizer "enhancements" in 19.9?

From: Mikhail Velikikh <mvelikikh_at_gmail.com>
Date: Wed, 17 Feb 2021 19:14:46 +0000
Message-ID: <CALe4Hpkz2OMu0FUoJdftWX-xe0_8-kvMPZ1BO3GF362Hv2dNZQ_at_mail.gmail.com>

>
> The interesting thing now is to see if it's possible to come up with an
> example where the bypass is necessary to avoid getting wrong results
> (because that's presumable why it's been introduced) and then to watch
> newer versions to see if the restriction is gradually relaxed.
>

I believe it is: Bug 30593046 - A query having a scalar subquery returned a wrong result (Doc ID 30593046.8)
https://support.oracle.com/rs?type=doc&id=30593046.8

The rationale for that is as follows:

1) it is fixed in 19.9;
2) it returns wrong results in previous release updates per the bug above;
3) it generates exactly the same bypass message in 19.9:

SQE: Query valid for SQ elimination:******* UNPARSED QUERY IS ******* SELECT "T1"."C1" "C1" FROM "TC"."T1" "T1" WHERE "T1"."C1">"T1"."C2"+ (SELECT /*+ UNNEST */ COUNT(*) "COUNT(*)" FROM "TC"."T2" "T2" WHERE "T2"."C1"="T1"."C1")
SQE: Query after SQ elimination:******* UNPARSED QUERY IS ******* SELECT "T1"."C1" "C1" FROM "TC"."T1" "T1" WHERE "T1"."C1">"T1"."C2"+ (SELECT /*+ UNNEST */ COUNT(*) "COUNT(*)" FROM "TC"."T2" "T2" WHERE "T2"."C1"="T1"."C1")
SU: Considering subquery unnesting in query block SEL$1 (#0)

---

Subquery Unnest (SU)

---

SU: Checking validity of unnesting subquery SEL$2 (#0)

*SU:     SU bypassed: More than 1 column in connect condition.*
SU:     SU bypassed: Failed basic validity checks.

SU: Validity checks failed.
SJC: Considering set-join conversion in query block SEL$1 (#0)

The fix appears to be a little restrictive, though. There are certain classes of queries which cannot take advantage of subquery unnesting anymore as this thread demonstrates.

On Wed, 17 Feb 2021 at 18:35, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> I see that Mikhail has supplied the explanation - and may have taken
> advantage of a Q&D I often use as the 2nd step to avoid looking too closely
> at a 10053
> ( https://jonathanlewis.wordpress.com/2014/05/14/feature-bypass/ )
>
> grep -n -i bypassed {10053 tracefile name}
>
> Also - for pre-emptive discovery from the executable - always check for
> new bypasses:
>
> strings -a oracle.exe | grep -i bypassed >bypassed.txt
>
>
> The interesting thing now is to see if it's possible to come up with an
> example where the bypass is necessary to avoid getting wrong results
> (because that's presumable why it's been introduced) and then to watch
> newer versions to see if the restriction is gradually relaxed.
>
> Regards
> Jonathan Lewis
>
>
>
>
>

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

Received on Wed Feb 17 2021 - 20:14:46 CET