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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *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@xxxxxxxxxxxxx)
*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
