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

Thank You Andrew.
I dont have the sql monitor for that exact path but I see from another captured sql monitor(in below location) post patch even the HASH join is happening between HISt and LASTHIST but still the query is crawling. And its the 'sort group by; consuming all the resources. https://gist.github.com/oracle9999/4b5ab7706cd7b0d42c3991977188ec6f

So it seems that the 'View pushed predicate' transformation is the one getting blocked for the merge query post patch. I am able to see that transformation if I run it as a SELECT query rather than a MERGE query. So wondering if still any possible hint can force the past execution path for this Merge query?.

Never played around the pathfinder , so I will need to check how to find the exact change which has happened because of this patch and causing this issue or else I hope 10053 trace will help to see if I get anything out of that.

Regards

On Thu, May 12, 2022 at 4:04 AM Andy Sayer <andysayer_at_gmail.com> wrote:

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