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

Do you not have a dev environment you can test things on?

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

> As we dont really want to run the MERGE/DML query on production. And also
> the MERGE query is not able to finish with the new path , So is it okay to
> just do like below explain plan to get the 10053 trace? Hoping that the
> MERGE query will not be executed behind the scene but just the plan will be
> generated. Please correct me if wrong here.
>
> alter session set tracefile_identifier='mergequeryplan';
>
> alter session set events='10053 trace name context forever, level 1';
>
> Explain plan for MERGE INTO HIST.....;
>
> alter session set events='10053 trace name context off';
>
>
> _at_Mohamed, Regarding the nested loop and hash join which is happening
> towards the end of the plan, yes both of the sql monitor which i posted for
> "post patch" are running slow.
>
> _at_Andy , Regarding the "nested loop outer" while MERGE INTO i.e at the top
> of the plan, If i see the before patch plan which was running fast, in that
> this was NESTED LOOP OUTER only. But yes as you said , i will try to force
> if at all i will be able to make that 'HASH JOIN OUTER' any way.
>
>
> On Thu, 12 May 2022, 3:15 pm Mohamed Houri, <mohamed.houri_at_gmail.com>
> wrote:
>
>> Pap
>>
>> You have shown two SQL monitors for the new plan one using a NESTED LOOP
>> and one using HASH JOIN both *without *VIEW PUSHED PREDICATE. Are both
>> plans bad for you? If not, then bear in mind that it is normal to not have
>> a VIEW PUSHED PREDICATE in the presence of a HASH JOIN. This transformation
>> is governed by NESTED LOOPS only.
>>
>> Therefore, if the case of the NESTED LOOPS poses a problem, you can get
>> the 10053 trace file to see why the VIEW PUSHED PREDICATE has not been
>> selected by Oracle.
>>
>> Best regards
>> Mohamed
>>
>> Le jeu. 12 mai 2022 à 11:08, Noveljic Nenad <nenad.noveljic_at_vontobel.com>
>> a écrit :
>>
>>> Hi Pap,
>>>
>>>
>>>
>>> The optimizer trace (event 10053) should reveal the reason for discarded
>>> JPPD.
>>>
>>>
>>>
>>> Best regards,
>>>
>>>
>>>
>>> Nenad
>>>
>>>
>>>
>>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
>>> Behalf Of *Andy Sayer
>>> *Sent:* Donnerstag, 12. Mai 2022 08:32
>>> *To:* Pap <oracle.developer35_at_gmail.com>
>>> *Cc:* Oracle L <oracle-l_at_freelists.org>
>>> *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. ***
>>>
>>> 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
>>>
>>>
>>>
>>> ____________________________________________________
>>>
>>> Please consider the environment before printing this e-mail.
>>>
>>> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>>>
>>>
>>> Important Notice
>>>
>>> This message is intended only for the individual named. It may contain
>>> confidential or privileged information. If you are not the named addressee
>>> you should in particular not disseminate, distribute, modify or copy this
>>> e-mail. Please notify the sender immediately by e-mail, if you have
>>> received this message by mistake and delete it from your system.
>>> Without prejudice to any contractual agreements between you and us which
>>> shall prevail in any case, we take it as your authorization to correspond
>>> with you by e-mail if you send us messages by e-mail. However, we reserve
>>> the right not to execute orders and instructions transmitted by e-mail at
>>> any time and without further explanation.
>>> E-mail transmission may not be secure or error-free as information could
>>> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
>>> processing of incoming e-mails cannot be guaranteed. All liability of
>>> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
>>> referred to as "Vontobel Group") for any damages resulting from e-mail use
>>> is excluded. You are advised that urgent and time sensitive messages should
>>> not be sent by e-mail and if verification is required please request a
>>> printed version.
>>> Please note that all e-mail communications to and from the Vontobel
>>> Group are subject to electronic storage and review by Vontobel Group.
>>> Unless stated to the contrary and without prejudice to any contractual
>>> agreements between you and Vontobel Group which shall prevail in any case,
>>> e-mail-communication is for informational purposes only and is not intended
>>> as an offer or solicitation for the purchase or sale of any financial
>>> instrument or as an official confirmation of any transaction.
>>> The legal basis for the processing of your personal data is the
>>> legitimate interest to develop a commercial relationship with you, as well
>>> as your consent to forward you commercial communications. You can exercise,
>>> at any time and under the terms established under current regulation, your
>>> rights. If you prefer not to receive any further communications, please
>>> contact your client relationship manager if you are a client of Vontobel
>>> Group or notify the sender. Please note for an exact reference to the
>>> affected group entity the corporate e-mail signature. For further
>>> information about data privacy at Vontobel Group please consult
>>> www.vontobel.com.
>>>
>>
>>
>> --
>>
>> Houri Mohamed
>>
>> Oracle DBA-Developer-Performance & Tuning
>>
>> Visit My - Blog <http://www.hourim.wordpress.com/>
>>
>> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
>> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>>
>> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
>> <https://twitter.com/MohamedHouri>
>>
>>

--
http://www.freelists.org/webpage/oracle-l