Re: Query runtime is slow in view

Hi Sayan and Lothar,

This is Oracle e-Business Suite seeded function, so I have to look out if management will allow any changes in the function code.

Best Regards,
Amit

On Wed, Feb 3, 2021 at 5:15 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi Amit,
>
> You can just to the definition of this function "deterministic" clause.
>
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning expert
> Oracle Database Developer Choice Award winner
> Oracle ACE Associate
> http://orasql.org
>
> чт, 4 февр. 2021 г., 0:19 Lothar Flatz <l.flatz_at_bluewin.ch>:
>
>> Hi Amit,
>>
>> my assumption is that the function calls SQL.
>> The way to fix the function is depending on th exact code.
>> The best option is to use sql instead of PL/SQL.
>> On version 19 you could use a sql macro.
>> Less good but easier alternatives are speeding up the PL/SQL by using the
>> result cache or tuning the sql inside of the function.
>>
>> Regards
>>
>> Lothar
>>
>> Am 03.02.2021 um 22:00 schrieb Amit Saroha:
>>
>> Thank you, everyone, for your replies. Based on the recommendations, I
>> changed the code, and the query is completed in less than 3 seconds now
>> (fetched all rows in sql developer). But after the below function call is
>> added instead of a hard-coded value, it again started taking the time.
>>
>> Could you suggest to me some way to fix this function call issue?
>> AND asn_lines.org_id = fnd_profile.value('ORG_ID')
>>
>> [image: image.png]
>>
>>
>> Best Regards,
>> Amit
>>
>>
>> On Wed, Feb 3, 2021 at 8:16 AM Laurentiu Oprea <
>> laurentiu.oprea06_at_gmail.com> wrote:
>>
>>> I`m sorry, please ignore my previous email, it is a wrong
>>> recommendation.
>>>
>>> În mie., 3 feb. 2021 la 11:49, Laurentiu Oprea <
>>> laurentiu.oprea06_at_gmail.com> a scris:
>>>
>>>> Can you try to add this hint /*+ USE_HASH(_at_"SEL$43B7898D"
>>>> "POH"_at_"SEL$6") */
>>>> Like already recommended make sure you fetch full result in sql
>>>> developer.
>>>> I see quite a lot of optimizer parameters disabled, interesting to find
>>>> how you reached to set them like this.
>>>>
>>>> În mie., 3 feb. 2021 la 11:37, Noveljic Nenad <
>>>> nenad.noveljic_at_vontobel.com> a scris:
>>>>
>>>>> Done.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> https://community.oracle.com/tech/apps-infra/discussion/4480388/unnesting-of-scalar-subqueries-in-select-clause
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> *From:* Lothar Flatz <l.flatz_at_bluewin.ch>
>>>>> *Sent:* Mittwoch, 3. Februar 2021 10:16
>>>>> *To:* Noveljic Nenad <nenad.noveljic_at_vontobel.com>
>>>>> *Cc:* ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
>>>>> *Subject:* Re: Query runtime is slow in view
>>>>>
>>>>>
>>>>>
>>>>> Did you place this as a database idea? (I might have even voted for it
>>>>> and not remember..)
>>>>>
>>>>> Am 03.02.2021 um 10:00 schrieb Noveljic Nenad:
>>>>>
>>>>> > which makes a rewrite cumbersome in some cases.
>>>>>
>>>>>
>>>>> A reason more to implement SSQ unnesting as an optimizer feature.
>>>>>
>>>>>
>>>>>
>>>>> Best regards,
>>>>>
>>>>>
>>>>>
>>>>> Nenad
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> *From:* Lothar Flatz <l.flatz_at_bluewin.ch> <l.flatz_at_bluewin.ch>
>>>>> *Sent:* Mittwoch, 3. Februar 2021 09:54
>>>>> *To:* Noveljic Nenad <nenad.noveljic_at_vontobel.com>
>>>>> <nenad.noveljic_at_vontobel.com>; Laurentiu Oprea
>>>>> <laurentiu.oprea06_at_gmail.com> <laurentiu.oprea06_at_gmail.com>;
>>>>> eramitsaroha_at_gmail.com
>>>>> *Cc:* ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
>>>>> <oracle-l_at_freelists.org>
>>>>> *Subject:* Re: Query runtime is slow in view
>>>>>
>>>>>
>>>>>
>>>>> Quite true. An Index is the quick fix. Rewrite is better because it
>>>>> allows the optimizer for more options.
>>>>> Thanks for pointing at the higher perspective, which also shows that
>>>>> indexing is not everything and maybe developers skills are still needed
>>>>> when automatic indexing is in place.
>>>>> Generally you just have to consider that a column value select is an
>>>>> implicit outer join which makes a rewrite cumbersome in some cases.
>>>>>
>>>>> Am 03.02.2021 um 09:40 schrieb Noveljic Nenad:
>>>>>
>>>>> > As the activity column in the sql Monitor suggests, time is almost
>>>>> exclusively spent in this column value subquery:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Scalar subquery (SSQ) in the select clause is generally a problem with
>>>>> large driving row sources. Unlike SQL Server, Oracle optimizer doesn’t
>>>>> unnest them, even in cases when that would be feasible.
>>>>>
>>>>>
>>>>>
>>>>> Many times, I got much better performance by manually rewriting such
>>>>> queries by replacing SSQ with either semi or outer join. It could be a
>>>>> better solution also in your case.
>>>>>
>>>>>
>>>>>
>>>>> Examples are listed here:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> https://nenadnoveljic.com/blog/correlated-subqueries-in-the-select-clause/
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> https://nenadnoveljic.com/blog/correlated-subqueries-in-the-select-clause-2/
>>>>>
>>>>>
>>>>>
>>>>> Best regards,
>>>>>
>>>>>
>>>>>
>>>>> Nenad
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ____________________________________________________
>>>>>
>>>>> 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.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> 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.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> 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.
>>>>>
>>>>
>>

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