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

Hi Pap,

That monitor is still dong a nested loop for the merge into HIST, we can see it’s managed it 226 times already. I want to see what happens when it hash joins to HIST so it only has to do that big group by + table scan once and the other access as a one off table scan.

Have a play with pathfinder, it might not find anything but if it does, still the bug no into MOS and see what’s relevant. It might be something safe, it might be something that’s saving you from corruption. If Oracle has turned off predicate push down in a merge condition because of a corruption bug and doesn’t have a work around then you are just going to have to try my alternatives.

Thanks,
Andy

On Thu, 12 May 2022 at 07:22, Pap <oracle.developer35_at_gmail.com> wrote:

> 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