Re: Optimizer "enhancements" in 19.9?

 Wow, you guys are amazing! This is excellent learning material, thank you!

Tanel, I was not aware of your "cofef" script, so that is very helpful. Just in my own cursory exploration, I have not yet identified any meaningful differences in parameters, but I will go through your blog article and look a little deeper. Also, given the information from Mikhail, I did not immediately find a fix_control for bug 30593046, but I'll keep looking.

Mikhail, Jonathan and Sayan, thanks for all this wonderful insight. Good stuff. I am still chewing on it. :)

On Wed, Feb 17, 2021 at 1:15 PM Mikhail Velikikh <mvelikikh_at_gmail.com> wrote:

> 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
>>
>>
>>
>>
>>

-- 
Charles Schultz

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