0

I have encountered Bug 34044661 - Poor Performance Due to SQL Not Unnesting in Oracle 19C (Doc ID 34044661.8).

Oracle provides some workarounds:

use UNNEST hint
or
alter session set "_optimizer_squ_bottomup" = false;
or
alter session set "_optimizer_cost_based_transformation" = OFF;

The current execution plan (estimate plan is 19h to execute).

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------------------
| 143 |   NESTED LOOPS                       |             |    307K|   135M|       |  1808M  (2)| 19:37:40 |
|*144 |    TABLE ACCESS FULL                 | XXXX        |   2571K|  1054M|       | 49880   (2)| 00:00:02 |
|*145 |    TABLE ACCESS FULL                 | BBBB        |      1 |    32 |       |   703   (2)| 00:00:01 |

After change "_optimizer_squ_bottomup" or "_optimizer_cost_based_transformation" (estimate plan is 05 secs to execute)

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------------------
|*146 |    HASH JOIN                         |             |    273K|   120M|    11M|   104K  (1)| 00:00:05 |
|*147 |     TABLE ACCESS FULL                | BBBB        |    273K|  8547K|       |   703   (2)| 00:00:01 |
|*148 |     TABLE ACCESS FULL                | XXXX        |   2571K|  1054M|       | 49880   (2)| 00:00:02 |

But I'd like to use the UNNEST hint. Where can I place this hint in the query? I haven't had any success so far to reach the second execution plan (without NESTED LOOPS).

The SQL is as follows (I've hidden some columns).

SELECT
 some columns,
 .....,
FROM XXXX AS FIN
WHERE NOT EXISTS  (SELECT 1 FROM BBBBB R1 WHERE R1.VVVV = 'F' AND R1.NNNN = FIN.NNNN)
AND NOT EXISTS (SELECT 1 FROM BBBBB R1 WHERE R1.VVVV = 'E' AND R1.NNNN = FIN.DDDDD)
AND NOT (FIN.SSSS = 'S' AND FIN.EEEEEE IS NOT NULL AND FIN.VVVV='E')
 UNION ALL
SELECT
 some columns,
 .....,
FROM XXXX AS FIN
,BBBBB CCC
WHERE (--
(CCC.VVVV = 'F' AND CCC.NNNN = FIN.NUFIN) OR
(CCC.VVVV = 'E' AND CCC.NNNN = FIN.DDDDD)
)
AND NOT (FIN.SSSS = 'S' AND FIN.EEEEEE IS NOT NULL AND FIN.VVVV='E');
Improve this question
2
  • How do you know you have hit Bug 34044661? Anyway, the docs suggest placing the hint in the relevant subquery, although they are a little light on detail.
    – William Robertson
    Commented Jul 23, 2023 at 11:02
  • We just upgraded from 11g to 19c, and the queries have significantly slowed down. Applying the workaround seemed to solve the problem. I believe we have encountered this bug. I already tried /*+ UNNEST */, but no change in execution plan.
    – Astora
    Commented Jul 23, 2023 at 13:20

1 Answer 1

Reset to default
3

Typically /*+ UNNEST */ is placed in the subquery.

Finding a seemingly better plan with "_optimizer_squ_bottomup" = false or "_optimizer_cost_based_transformation" = OFF; (especially this one) does not necessarily mean you have the specific problem the referenced note describes.

There is a huge difference between enabling/disabling a single transformation and disabling all cost-based transformations.

Improve this answer

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