Re: Query runtime is slow in view

  • From: Lothar Flatz <l.flatz@xxxxxxxxxx>
  • To: Sayan Malakshinov <xt.and.r@xxxxxxxxx>, Mohamed Houri <mohamed.houri@xxxxxxxxx>
  • Date: Thu, 4 Feb 2021 10:41:11 +0100

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/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/03/13/optimization-of-loops-in-plsql-part-1/>

http://orasql.org/2013/06/10/too-many-function-executions/ <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 ;<http://orasql.org>

чт, 4 февр. 2021 г., 10:14 Mohamed Houri <mohamed.houri@xxxxxxxxx <mailto:mohamed.houri@xxxxxxxxx>>:


    /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/
    
<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@xxxxxxxxx <mailto:xt.and.r@xxxxxxxxx>> 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 Twitter <https://twitter.com/MohamedHouri> -MohamedHouri
    <https://twitter.com/MohamedHouri>


Other related posts: