Sudden plan change related to "VIEW PUSHED PREDICATE" with cost difference of 1M VS 13G
Date: Thu, 12 May 2022 00:29:55 +0530
Message-ID: <CAEjw_fhxx00grLZa2LE+Ng+WGyzyqjhmjR9imJtY4PDuEnrcww_at_mail.gmail.com>
In one of the 12.1.0.2.0 version oracle databases we are suddenly seeing one of the MERGE query changed the plan and that plan is no longer reproduced. This used to finish in ~5-6minutes now running for 3-4hours but still not finishing. And checking the dba_hist_sqlstat and dba_hist_active_sess_history we found that this change in plan happened after the team applied the April 2021 patch. We tried applying baseline and profile but none of them were able to change the plan back to before. Even we tried passing the exact outline through a sql profile but still it's not producing the plan.
Looking into the query plan it appears the plan_line_id- 25 i.e. "VIEW PUSHED PREDICATE" is no longer happening even if it's forced. And the difference in cost between the two plans is vast i.e. ~1M vs 13G. So wondering what exact transformation is blocked post the patch which is making this plan deviation.
From the outline section i see the , old plan was having "OPT_PARAM('_fix_control' '26664361:2')" but the new plan won't have this fix_control but again , even by forcing this through hints/profile the old plan is not getting reproduced.So wanted to understand what has cause this plan deviation and if we can get this back or fix the query some way?
Below is the link to the plan and outline of both the old and the new plan with sample sql monitor.
I captured the complete execution of the old plan sql monitor but just ~10minutes of the execution of the new plan sql monitor.
https://gist.github.com/oracle9999/e0368a252195d84beb1e9ae847094993
-- http://www.freelists.org/webpage/oracle-lReceived on Wed May 11 2022 - 20:59:55 CEST