Re: Query runtime is slow in view

Dear All,

Thank you for all the great insights. I have figured out a workaround using a pass-through table 'HR_OPERATING_UNITS' which has only three rows. I used the function to compare org_id against three rows of the pass-through table instead of invoking a function against thousand rows. The query is completing within the expected time now.

Best Regards,
Amit

On Thu, Feb 4, 2021 at 6:28 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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