Home -> Mailing Lists -> Oracle-L -> Re: Query runtime is slow in view

Re: Query runtime is slow in view

From: Amit Saroha <eramitsaroha_at_gmail.com>
Date: Wed, 3 Feb 2021 16:00:49 -0500
Message-ID: <CAG67e6TN1SXx282jZ2syX1NkTG-O-4vQHCPXOSyxi8nZvrW79Q_at_mail.gmail.com> 


















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













Received on Wed Feb 03 2021 - 22:00:49 CET












Original text of this message