RE: Sudden plan change related to "VIEW PUSHED PREDICATE" with cost difference of 1M VS 13G

From: Pap <oracle.developer35_at_gmail.com> Sent: Freitag, 13. Mai 2022 07:21
To: Noveljic Nenad <nenad.noveljic_at_vontobel.com> Cc: Oracle L <oracle-l_at_freelists.org>; Andy Sayer <andysayer_at_gmail.com>; Mohamed Houri <mohamed.houri_at_gmail.com> Subject: Re: Sudden plan change related to "VIEW PUSHED PREDICATE" with cost difference of 1M VS 13G

• E-Mail from outside Vontobel: Do not click on links or open attachments unless you know the content is safe. *** _at_Nenad "Are there any lines between the two lines mentioned above (“updated best state” and “Will not use JPPD” that might give the reason for not using JPPD? " There are no lines between these two in the trace file.

On Fri, May 13, 2022 at 1:45 AM Noveljic Nenad <nenad.noveljic_at_vontobel.com<mailto:nenad.noveljic_at_vontobel.com>> wrote: Hello Pap

I revisited my suggestion about column statistics - it was wrong. The low selectivity led to the JPPD transformation cost similar to the one that was in the plan with JPPD. JPPD: Updated best state, Cost = 39.073380

If we multiply the cost above with the number of rows of the outer query block and add the cost of the outer join query block, we get the total cost of the plan with JPPD. It’s much lower than the plan without JPPD and similar to the cost of the good plan you’ve had previously: 20710 * 39.073380 + ~260K ~ 1M

So JPPD should definitely be selected.

The optimizer figured out that correctly and updated the best state with the cost of JPPD (39.07 is the cost of a single iteration):

JPPD: Updated best state, Cost = 39.073380

But then, it discarded it soon, for still unknown reason:

JPPD: Will not use JPPD from query block SEL$B29E968D

You mentioned that you edited out some lines from the optimizer trace. Are there any lines between the two lines mentioned above (“updated best state” and “Will not use JPPD” that might give the reason for not using JPPD? As already mentioned, the cost isn’t the reason.

If not, the following might be the reason:

A Optimizer applied some heuristics without logging it or

B The correct decision got overridden due to a bug

Could you try following:

• Generate the plan without optimizer_features_enabled (according to the outline it is set to 11.2.0.4)
• Remove the ORDERED hint and check if it has any impact
• Duplicate the database and upgrade it to 19c just to see if the problem reproduces in a newer release

Best regards,

Nenad

Important Notice