12.2 bug for ANSI outer join syntax

["" (Redacted sender "Jay.Miller" for DMARC)]

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

[Andy Sayer]

Left outer joins to subqueries are common, are you sure the scenario is as
simple as that?

Was there an execution plan? Perhaps the optimizer had correlated the
subquery whereas that needs to not happen for your query performance to be
acceptable (or vice versa).

Have you got an end-to-end demo you can share?

Regards,
Andy

["" (Redacted sender "Jay.Miller" for DMARC)]

I should have time to do some testing next week. I hope to prune the query down quite a bit to make it simpler/more legible. I do have both execution plans though I had use EXPLAIN PLAN to get the 12.2 one as the query never completed. They are similar but not identical.

Fortunately since optimizer_features_enable is settable on the session level it will be easy to test.

Jay Miller
Sr. Oracle DBA
201.369.8355

From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Andy Sayer
Sent: Friday, August 10, 2018 3:06 PM
To: dmarc-***@freelists.org
Cc: oracle-***@freelists.org
Subject: Re: 12.2 bug for ANSI outer join syntax

Left outer joins to subqueries are common, are you sure the scenario is as simple as that?

Was there an execution plan? Perhaps the optimizer had correlated the subquery whereas that needs to not happen for your query performance to be acceptable (or vice versa).

Have you got an end-to-end demo you can share?

Regards,
Andy

[Andy Sayer]

Sounds like a good path to try.

Explain plan might be reliable enough to point out the issue, otherwise you
could search for the appropriate sql_id in v$sql and call
dbms_xplan.display_cursor with the sql_id explicitly passed to it.

It’s possible your left join is conditional and you’re suffering from
something like what I talk about in
https://ctandrewsayer.wordpress.com/2018/06/06/conditional-outer-joins-forcing-nested-loops/
I.e a join that looks like it ought to be done by a hash join is really
nested looped. It might be worse than that and the join predicate is not
being pushed all the way to act against the table (and take advantage of
indexes) But is going against the subquery as a non mergeable view instead.

If this is something that has only started happening then maybe there’s
some funny transformations occurring around lateral views. There were some
bugs surrounding them originally, it’s possible that they’ve been fixed but
at the cost of performance.

If you share how the join is performed in the plan (along with the
predicates section), then that could help an explanation.

Post by "" (Redacted sender "Jay.Miller" for DMARC)
I should have time to do some testing next week. I hope to prune the query
down quite a bit to make it simpler/more legible. I do have both execution
plans though I had use EXPLAIN PLAN to get the 12.2 one as the query never
completed. They are similar but not identical.
Fortunately since optimizer_features_enable is settable on the session
level it will be easy to test.
Jay Miller
Sr. Oracle DBA
201.369.8355
*Sent:* Friday, August 10, 2018 3:06 PM
*Subject:* Re: 12.2 bug for ANSI outer join syntax
Left outer joins to subqueries are common, are you sure the scenario is as simple as that?
Was there an execution plan? Perhaps the optimizer had correlated the
subquery whereas that needs to not happen for your query performance to be
acceptable (or vice versa).
Have you got an end-to-end demo you can share?
Regards,
Andy

["" (Redacted sender "Jay.Miller" for DMARC)]

The sql_id never shows up in v$sql despite displaying in OEM while it runs which makes things more difficult. In hindsight was a clue that something strange was going on. It started happening as soon as we migrated from 12.1 to 12.2.

I’m working on trimming the SQL down, so far I’ve eliminated about 60% of it and am still able to reproduce the issue. Will keep working on it later this afternoon. I’ll provide the EXPLAIN PLAN output once it’s small enough that be easily legible.




Jay Miller
Sr. Oracle DBA
201.369.8355

From: Andy Sayer [mailto:***@gmail.com]
Sent: Friday, August 10, 2018 6:58 PM
To: Miller, Jay
Cc: dmarc-***@freelists.org; oracle-***@freelists.org
Subject: Re: 12.2 bug for ANSI outer join syntax

Sounds like a good path to try.

Explain plan might be reliable enough to point out the issue, otherwise you could search for the appropriate sql_id in v$sql and call dbms_xplan.display_cursor with the sql_id explicitly passed to it.

It’s possible your left join is conditional and you’re suffering from something like what I talk about in
https://ctandrewsayer.wordpress.com/2018/06/06/conditional-outer-joins-forcing-nested-loops/<https://urldefense.proofpoint.com/v2/url?u=https-3A__ctandrewsayer.wordpress.com_2018_06_06_conditional-2Douter-2Djoins-2Dforcing-2Dnested-2Dloops_&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=wF2FXjoCVstv5FWb8tXTkNYblqCjZLiVty4VuSaEDXY&s=i8ylVFmrNk5ON3iPV-C-aFMwgraH2IvWuMSnH2x_a1w&e=> I.e a join that looks like it ought to be done by a hash join is really nested looped. It might be worse than that and the join predicate is not being pushed all the way to act against the table (and take advantage of indexes) But is going against the subquery as a non mergeable view instead.

If this is something that has only started happening then maybe there’s some funny transformations occurring around lateral views. There were some bugs surrounding them originally, it’s possible that they’ve been fixed but at the cost of performance.

If you share how the join is performed in the plan (along with the predicates section), then that could help an explanation.

On Fri, 10 Aug 2018 at 21:50, <***@tdameritrade.com<mailto:***@tdameritrade.com>> wrote:
I should have time to do some testing next week. I hope to prune the query down quite a bit to make it simpler/more legible. I do have both execution plans though I had use EXPLAIN PLAN to get the 12.2 one as the query never completed. They are similar but not identical.

Fortunately since optimizer_features_enable is settable on the session level it will be easy to test.

Jay Miller
Sr. Oracle DBA
201.369.8355

From: oracle-l-***@freelists.org<mailto:oracle-l-***@freelists.org> [mailto:oracle-l-***@freelists.org<mailto:oracle-l-***@freelists.org>] On Behalf Of Andy Sayer
Sent: Friday, August 10, 2018 3:06 PM
To: dmarc-***@freelists.org<mailto:dmarc-***@freelists.org>
Cc: oracle-***@freelists.org<mailto:oracle-***@freelists.org>
Subject: Re: 12.2 bug for ANSI outer join syntax

Left outer joins to subqueries are common, are you sure the scenario is as simple as that?

Was there an execution plan? Perhaps the optimizer had correlated the subquery whereas that needs to not happen for your query performance to be acceptable (or vice versa).

Have you got an end-to-end demo you can share?

Regards,
Andy

[David Barbour]

Good Morning,

We upgraded to 12.2. in our development environment in June and have been
extensively testing our code without encountering this issue. I ran this
by our developers and they supplied me with code containing a nested
sub-query using the left outer join. In the code we're using, the 'ON'
component of the join occurs after the the actual query portion:

left outer join ( select ar.iuser_oid
from assessment_result ar
join skill on skill.oid = ar.skill_oid
join dbstring on dbstring.oid = skill.module_name_oid and
mnemonic = 'ISIP_ARPM'
where ar.serial = 0 and
ar.period = :PERIOD {0}
having count(ar.oid) >=
:ARPMSKILLCOUNT
group by ar.iuser_oid
) arfiltered
on arfiltered.iuser_oid =
membership.oid

There is a similar example on stackoverflow at:
https://stackoverflow.com/questions/14571254/ora-01799-a-column-may-not-be-outer-joined-to-a-subquery

It seems that if the 'ON' clause is self-contained, the error does not
appear. If I re-write our code to:

left outer join assessment_result ar
on
(subquery)

the query returns the error code.

Just thought it was interesting.

Thanks for the heads-up!



On Mon, Aug 13, 2018 at 10:50 AM, Redacted sender Jay.Miller for DMARC <

Post by "" (Redacted sender "Jay.Miller" for DMARC)
The sql_id never shows up in v$sql despite displaying in OEM while it runs
which makes things more difficult. In hindsight was a clue that something
strange was going on. It started happening as soon as we migrated from 12.1
to 12.2.
I’m working on trimming the SQL down, so far I’ve eliminated about 60% of
it and am still able to reproduce the issue. Will keep working on it later
this afternoon. I’ll provide the EXPLAIN PLAN output once it’s small enough
that be easily legible.
Jay Miller
Sr. Oracle DBA
201.369.8355
*Sent:* Friday, August 10, 2018 6:58 PM
*To:* Miller, Jay
*Subject:* Re: 12.2 bug for ANSI outer join syntax
Sounds like a good path to try.
Explain plan might be reliable enough to point out the issue, otherwise
you could search for the appropriate sql_id in v$sql and call
dbms_xplan.display_cursor with the sql_id explicitly passed to it.
It’s possible your left join is conditional and you’re suffering from
something like what I talk about in
https://ctandrewsayer.wordpress.com/2018/06/06/conditional-outer-joins-
forcing-nested-loops/
<https://urldefense.proofpoint.com/v2/url?u=https-3A__ctandrewsayer.wordpress.com_2018_06_06_conditional-2Douter-2Djoins-2Dforcing-2Dnested-2Dloops_&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=wF2FXjoCVstv5FWb8tXTkNYblqCjZLiVty4VuSaEDXY&s=i8ylVFmrNk5ON3iPV-C-aFMwgraH2IvWuMSnH2x_a1w&e=>
I.e a join that looks like it ought to be done by a hash join is really
nested looped. It might be worse than that and the join predicate is not
being pushed all the way to act against the table (and take advantage of
indexes) But is going against the subquery as a non mergeable view instead.
If this is something that has only started happening then maybe there’s
some funny transformations occurring around lateral views. There were some
bugs surrounding them originally, it’s possible that they’ve been fixed but
at the cost of performance.
If you share how the join is performed in the plan (along with the
predicates section), then that could help an explanation.
I should have time to do some testing next week. I hope to prune the query
down quite a bit to make it simpler/more legible. I do have both execution
plans though I had use EXPLAIN PLAN to get the 12.2 one as the query never
completed. They are similar but not identical.
Fortunately since optimizer_features_enable is settable on the session
level it will be easy to test.
Jay Miller
Sr. Oracle DBA
201.369.8355
freelists.org] *On Behalf Of *Andy Sayer
*Sent:* Friday, August 10, 2018 3:06 PM
*Subject:* Re: 12.2 bug for ANSI outer join syntax
Left outer joins to subqueries are common, are you sure the scenario is as simple as that?
Was there an execution plan? Perhaps the optimizer had correlated the
subquery whereas that needs to not happen for your query performance to be
acceptable (or vice versa).
Have you got an end-to-end demo you can share?
Regards,
Andy