RE: 12.2 bug for ANSI outer join syntax

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:andysayer_at_gmail.com] Sent: Friday, August 10, 2018 6:58 PM
To: Miller, Jay
Cc: dmarc-noreply_at_freelists.org; oracle-l_at_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, <Jay.Miller_at_tdameritrade.com<mailto:Jay.Miller_at_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-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Andy Sayer Sent: Friday, August 10, 2018 3:06 PM
To: dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org> Cc: oracle-l_at_freelists.org<mailto:oracle-l_at_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

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