12.2 bug for ANSI outer join syntax
Date: Fri, 10 Aug 2018 17:38:09 +0000
Message-ID: <0D8F4CAC0F9D3C4AACC63F50FD9957F7548498EA_at_PRDCTWPEMLMB31.prod-am.ameritrade.com>
It seems we may have been the first to hit a new bug in 12.2 and I thought I’d share it with the list in case anyone else runs into it.
Symptoms were that after migrating from 12.1 to 12.2 we had a few queries not completing. Most of them were fixed by just importing a sql baseline from the 12.1 environment but there was 1 hideously complex query where that didn’t work. Even though OEM showed the baseline being used it was still using a different plan_hash_value. Running the SQL Tuning advisor gave the error that the sql id did not exist.
What led me to the solution was when I reviewed the previous SQL Tuning Advisor runs it showed a different error – ORA 1799 - a column may not be outer-joined to a subquery
Now this is not supposed to be a problem in current versions. But apparently if you use ANSI outer join syntax (e.g. LEFT OUTER JOIN) rather than Oracle syntax (+) in 12.2 and join to a subquery then the SQL will hang. It won’t abort and it will show as consuming cpu but it will never complete.
I haven’t tested with shorter/simpler queries yet, I’ll probably do that sometime next week.
Short term I fixed the problem by setting optimizer_features_enable to 12.1.0.2 after which the query completed in its usual time. Longer term they will be rewriting their SQL to use Oracle syntax.
HTH someone.
Jay Miller
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 10 2018 - 19:38:09 CEST