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/ 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> 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] *On Behalf Of *Andy Sayer
> *Sent:* Friday, August 10, 2018 3:06 PM
> *To:* dmarc-noreply_at_freelists.org
> *Cc:* 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