How to prevent UNPIVOT from being transformed into UNION ALL

oracleoracle-12cunionunpivot

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 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.

Best Answer

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.

Related Solutions

Why does the query cost change so much and how to prevent it

Most likely your tables have up-to-date statistics but sometimes the optimizer is baffled because it oversimplifies the cardinality estimation.

This seems to be a good candidate for dynamic sampling. In its default value (2 in 10g and 11g), dynamic sampling will only be used if one of the table has no statistics. In your case you would need to change its value to be able to let the optimizer collect statistics to build a better plan.

I suggest you use the DYNAMIC_SAMPLING hint that will let you modify the optimizer behaviour for a single query. I tested with a subquery and you need to use one of the following syntax:

the full hint directly on the top of the query, this will sample all tables, which will definitely work but may take too much time.
```
SELECT /*+ DYNAMIC_SAMPLING (10) */ FROM ...
```

the full hint on the subquery:

SELECT
  ... a lot of stuff ...
WHERE
  ... lots of complex clauses ...
  AND my_id in ( select /*+ DYNAMIC_SAMPLING (10) */ my_id 
                   from small_table 
                  where .. something simple ..)

the hint with a query block name:

SELECT /*+ DYNAMIC_SAMPLING (@my_block 10) */
  ... a lot of stuff ...
WHERE
  ... lots of complex clauses ...
  AND my_id in ( select /*+ QB_NAME(my_block) */ my_id 
                   from small_table 
                  where .. something simple ..)

The second and third option should produce the same result: sampling only on one table.

Different execution plan for the same query if I change a value in the predicate

The problem was histograms, I ran statistics and disabled histogram creation and the execution plan used nested loops:

BEGIN
  DBMS_STATS.GATHER_table_STATS (OWNNAME => 'MIDAS', TABNAME => 'MINCISOC', 
  METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;

If I run it with FOR ALL COLUMNS SIZE AUTO again the same problem because it uses hash join. Thanks to Phil for the suggestion.

Best Answer

Related Solutions

Why does the query cost change so much and how to prevent it

Different execution plan for the same query if I change a value in the predicate

Related Question