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:
Here is the same logic when run as part of the full query:
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
UNION ALL? I am on Oracle 188.8.131.52.