RE: Query runtime is slow in view

I would check with the E-biz folks to see whether they have a “best” advisory. They have nice blogs and an actual interest in making E-biz easy to use. OAUG folks may want to chime in. Everything I write here may be significantly changed by time.  

A couple very dated notes from me:  

1. The “FND” packages bundles tend to be somewhat large and many functions and procedures are called often enough to be a pain in the used memory hole but not often enough to be friendly about to staying in. As an edge case to avoid having to find a big enough chunk dynamically, it may well be useful overall for you to keep and pin the package on database restarts when you intend to move on to starting up the applications (ie. starting the concurrent manager and moving the “we’re doing maintenance” top menu replacement aside in favor of the actual top menu). Making this a non-first step in the database restart sequence is useful for when you are doing maintenance cycles where you don’t want the time delay of revving everything up and having to shut it down before the database will close cleanly. This practice can be useful, for example, in 9-5 human activity using all those forms and packages frequently getting pushed out during batch overnight followed by a crappy memory storm from 9 to 9:30, starting everyone out with a crappy day. Memory is cheap. Oracle manages memory pretty well in the average case, but e-Biz is not the average case.
2. The database level result cache may be appropriate for things like “profile” that tend to change very slowly.

You probably want to avoid customizations. Quoting Sheldon Gregory: “Customizations are like herpes: Once you have them, you are responsible for them and you probably cannot make them go away.”  

I don’t know whether E-biz ever implemented a package by package “do-nothing” function to be specific about keeping packages. You usually can find something innocuous that does not make changes to get the packages you want parsed and valid into memory. (Aside: If you’re implementing your own large packages, put a “PIN_ME” function in there so that IF this turns out to be your users’ need, they have it.)  

Unless you have an application with this “semi-frequent, but I’m gonna use you again and I’m big enough to be a pain” suite of packages, Oracle’s normal mechanism is probably good enough.  

This bit of pre-emptive over engineering is something I always did implementing E-biz, and my customers never experienced a large class of problems folks who didn’t do this did experience (centered around “I don’t have a big enough chunk of memory for that” errors). That may also be because of good luck in client selection.  

Your mileage may vary. I believe it is still true that marking a package as keep does not bring the package in, so you need to do something to bring it in on restarts if you would like to eliminate any cycling. IF you can’t find a convenient function to call painlessly, you may be able to find something that doesn’t invalidate the universe to re-compile once per restart (and restarts should be infrequent.)  

Good luck,  

mwf  

PS: Don’t customize off the shelf suites. Use users groups to demand configuration ability through your installation data rather than through changing the code. IF a piece of their code shows up functioning correctly but being slow in elapsed time or file it as a performance bug and follow up with the users group.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lothar Flatz Sent: Thursday, February 04, 2021 4:41 AM To: Sayan Malakshinov; Mohamed Houri
Cc: Amit Saroha; Laurentiu Oprea; Noveljic Nenad; ORACLE-L (oracle-l_at_freelists.org) Subject: Re: Query runtime is slow in view  

If this queries the database I am not sure it can be deterministic. Instead I suggest a wrapper function that uses the result cache. Best would be a function in the with clause to minimise context switch.

Regards

Lothar

Am 04.02.2021 um 08:58 schrieb Sayan Malakshinov:

Hi Mohamed,  

+my posts describing how does it work:  

Deterministic Functions and Scalar Subquery Caching:

<http://orasql.org/2013/02/10/deterministic-function-vs-scalar-subquery-caching-part-1/> http://orasql.org/2013/02/10/deterministic-function-vs-scalar-subquery-caching-part-1/

<http://orasql.org/2013/02/11/deterministic-function-vs-scalar-subquery-caching-part-2/> http://orasql.org/2013/02/11/deterministic-function-vs-scalar-subquery-caching-part-2/

<http://orasql.org/2013/03/13/deterministic-function-vs-scalar-subquery-caching-part-3/> http://orasql.org/2013/03/13/deterministic-function-vs-scalar-subquery-caching-part-3/

http://orasql.org/2014/03/31/deterministic-functions-result_cache-and-operators/  

http://orasql.org/2013/03/13/optimization-of-loops-in-plsql-part-1/  

http://orasql.org/2013/06/10/too-many-function-executions/  

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE Associate
http://orasql.org  

чт, 4 февр. 2021 г., 10:14 Mohamed Houri <mohamed.houri_at_gmail.com>:  

or you can just wrap it into scalar subquery ... = (select fnd_profile.value('ORG_ID') from dual)

for scalar subquery caching  

That's exactly what I have explained in the above-mentioned blog post  

https://hourim.wordpress.com/2019/12/18/scalar-subquery-caching-the-select-from-dual-trick/  

Best regards

Mohamed      

Le jeu. 4 févr. 2021 à 00:27, Sayan Malakshinov <xt.and.r_at_gmail.com> a écrit :

or you can just wrap it into scalar subquery ... = (select fnd_profile.value('ORG_ID') from dual)

for scalar subquery caching  

-- 

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  <https://twitter.com/MohamedHouri> Twitter      - MohamedHouri <https://twitter.com/MohamedHouri> 

 





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