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@xxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Andy Sayer
*Sent:* Friday, August 10, 2018 3:06 PM
*To:* dmarc-noreply@xxxxxxxxxxxxx
*Cc:* oracle-l@xxxxxxxxxxxxx
*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
