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
