13

I have a somewhat complex Oracle query which is taking about half an hour to complete. If I take the slow part of the query and run it separately it finishes in a few seconds. Here's is a screenshot of the SQL Monitor report for the isolated query:

ok plan

Here is the same logic when run as part of the full query:

bad plan

The colors correspond to the same tables in both screenshots. For the slow query, Oracle is doing a MERGE JOIN between two tables that do not have an equality condition in the JOIN. As a result of that, about 150 million intermediate rows are unnecessarily processed.

I am able to work around this problem with query hints or rewrites, but I want to understand as much of the root cause as I can so that I can avoid this problem in the future and possibly submit a bug report to Oracle. Every time I get the bad plan the UNPIVOT in the query text is transformed into a UNION ALL in the plan. To further investigate I would like to prevent that query transformation from happening. I have been unable to find a name for this transformation. I also have not been able to find a query hint or underscore parameter that will prevent it. I'm testing on a development server so anything goes.

Is there anything that I can do to prevent the query transformation of the UNPIVOT to UNION ALL? I am on Oracle 12.1.0.2.

I am not able to share the query, table names, or data for IP reasons. I was not able to come up with a simple reproduction. With that said, it's unclear to me why that information is needed to answer the question. Here is an example of an UNPIVOT query along with the same query implemented as UNION ALL.

  • 1
    In your example union all exists hence it shows. I ran my own query that doesn't have a union all and explain plan doesn't show a union all. So, perhaps it is your query and not the unpivot operation that is causing the problem here? – Raj May 17 '17 at 15:42
  • @Raj The point is to disable that transformation without changing the query text in order to further narrow down the problem. – Joe Obbish May 17 '17 at 19:17
  • 3
    I have been working with Oracle for many years, but sometimes i need to see the query or atleast compatible pseudo query with your columns/tables substituted. without which I can keep guessing but it is neither useful to you nor to me. – Raj May 18 '17 at 0:49
1

Try the following Oracle optimizer hint:

NO_EXPAND

This hint disables OR-expansion. OR-expansion transforms combined OR-conditions or IN-lists in the where clause into a compound query with the union all.

We don't have your SQL so this is a guess but seems to a viable option.

As a second option, since you are in a development environment, you can try the Oracle optimizer hint:

NO_QUERY_TRANSFORMATION

but this hint disables all query transformations that the optimizer can perform other than transformations that the optimizer always can apply.

  • OR is not the same thing as UNPIVOT. Can you produce a simple example that shows the NO_EXPAND hint working for UNPIVOT? I have tried this and it didn't not work for me. – Joe Obbish Jul 14 '17 at 19:55
  • @JoeObbish - Can you provide a scaled-down version of your complex SQL with the UNPiVOT, please? I'd like to help you if I can... – tale852150 Jul 14 '17 at 20:39
  • 1
    @JoeObbish - updated answer with another suggestion. Hope this helps. – tale852150 Jul 16 '17 at 1:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.