Re: Query runtime is slow in view

Hi Lothar,

If this queries the database I am not sure it can be deterministic.

I'm not sure what exactly you mean: impossibility to define it as deterministic or deterministic nature in the meaning of "pure function"? "Deterministic" clause just *declares* that this function is "deterministic", so it allows Oracle to optimize such function calls like they must return the same results for the same arguments, i.e. it allows to reduce the number of function calls in such cases. I know only 2 such optimizations:
1. caching their results in SQL between fetch calls <http://orasql.org/2013/02/10/deterministic-function-vs-scalar-subquery-caching-part-1/> (for
literals it works better:
http://orasql.org/2014/03/31/deterministic-functions-result_cache-and-operators/ ),
2. move them out from PL/SQL loops (
http://orasql.org/2013/03/13/optimization-of-loops-in-plsql-part-1/ ) So, considering that pl/sql functions are not read consistent to the query (even inline pl/sql functions:
http://orasql.org/2013/07/03/oracle-12c-inconsistency-of-inline-functions/), adding "deterministic" clause just reduces inconsistency.

Instead I suggest a wrapper function that uses the result cache.

Amit asks about a function with a literal in the input arguments. Consider simple example:
select f1(1), f2(1), f3(1), (select t.b from t where t.a=1) sq from dual connect by level<=100;

where f1 is declared as a simple function that returns 'select t.b from t where t.a=f1.a',
f2 is the same as f1 but with "deterministic" clause and f3 is the same as f1 but with result_cache clause. Table T contains just 1 row: a=1, b=1

You start this query and after first 50 rows another session updates table t: update t set b=0;commit;
So first 50 results of f1 and f3 (result cache) will be equal to 1 and other 50 - 0, though "SQ" and all 100 return values of f2 will be equal to original value 1, which is consistent to the scn of the query start time. In addition, result cache is a pretty complex thing: a lot of restrictions, dependency tracking
<http://orasql.org/2015/07/05/result_cache-run-time-dependency-tracking/>, latches, invalidations, memory allocation, etc.. so it's slower than simple scalar subquery or deterministic functions caching in case of many calls with the same constant input argument in a query. Of course, it's good when you want to share slowly changed cached results between all sessions, but it doesn't mean that we can use both of them: deterministic + result_cache.

Obviously, if your query calls a pl/sql function containing other queries, it's better to create and use SQL operator on it for read consistency (but it kills performance).

Best would be a function in the with clause to minimise context switch.

 Inline pl/sql functions also causes context switches, but they are much faster:
http://orasql.org/2013/07/13/oracle-12c-behavior-tests-of-the-inline-functions-identities-and-defaults/

PS. There is also interesting behaviour of sql result cache with non-deterministic function in a query:
http://orasql.org/2015/07/05/a-function-gets-called-twice-if-the-result_cache-is-used/

>
> *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/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/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
>
>
>
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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