Re: Sudden plan change related to "VIEW PUSHED PREDICATE" with cost difference of 1M VS 13G
Date: Wed, 11 May 2022 23:34:18 +0100
Message-ID: <CACj1VR6dodSndOdoN54txymhs4j=T0Oc+BknYiZ_x6X=hfo03Q_at_mail.gmail.com>
Most likely explanation is there was a bug fix which turns off this plan route completely. You can have a play with Pathfinder https://mauro-pagano.com/2015/10/26/introducing-pathfinder-is-there-a-better-plan-for-my-sql/ to see if there is something you can turn on/off.
Reading the plan, it strikes me that a hash join to HIST and the LASTHIST subquery would actually be better. Strangely, the new execution plan you shared had the hash join to this subquery but it didn't in the live monitor. What's the performance like when you hint: USE_HASH(_at_"SEL$B29E968D" "VW_SQ_1"_at_"SEL$E9784550") USE_HASH(_at_"SEL$1" "from$_subquery$_014"_at_"SEL$1")
(you might need a few more merge-y hints to get the right thing going on).
Another option you have is to rewrite the query so that this is done inside your TINFO subquery, this looks possible as all the join conditions are inside the subquery. Here's my bash at it:
MERGE INTO HIST
USING (SELECT /*+ordered*/
DISTINCT FS.PEID, VTI.PCODE, VTI.MCD MCD,
VT.PBCNT,VT.BAMT BASEAM, VTC.TCNT TCNT,VTC.TAMT TAMT
,(select max (ot_dt)
from hist lasthist
where lasthist.fsid = fs.peid
and lasthist.scd = vti.pcode
and lasthist.bas_cn = vt.pbcnt
and lasthist.bamt = vt.bamt
and lasthist.txn_cn = vtc.tcnt
and lasthist.tamt = vtc.tamt
) max_OT_DT
FROM FS , VT , VTC , VTI
WHERE VT.PVID = VTC.PVID
AND VT.PVID = VTI.PVID
AND VTC.PVID = VTI.PVID
AND ( (VT.PVGID = FS.PVGID)
OR (VT.PVGID = FS.OVTGID))) TINFO
ON ( HIST.FSID = TINFO.PEID AND HIST.SCD = TINFO.PCODE
AND HIST.MCD = TINFO.MCD
AND HIST.BAS_CN = TINFO.PBCNT AND HIST.BAMT = TINFO.BASEAM
AND HIST.TXN_CN = TINFO.TCNT
AND HIST.TAMT = TINFO.TAMT
AND HIST.OT_DT = TINFO.max_OT_DT)
WHEN NOT MATCHED
THEN
INSERT (OT_DT, FSID, SCD,MCD,BAS_CN,BAMT, TXN_CN, TAMT, CRDT_TS,CRE_USR_ID)
VALUES ( sysdate, PEID, PCODE, MCD,PBCNT,BASEAM, TCNT,TAMT, SYSDATE,'123') Hope that helps!
Andy
On Wed, 11 May 2022 at 20:00, Pap <oracle.developer35_at_gmail.com> wrote:
> 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 Thu May 12 2022 - 00:34:18 CEST