Re: Should we use Subquery caching or result cache here

Thank you All. It helped.

Didn't get a chance to test all the options as it's a third party app. But as we tested two options i.e. 1) with only scalar subquery caching on the Insert as select query and no other changes to function 2) Replacing the 'select from.. dual' query inside the function with just simple assignment operator + Scalar subquery caching on the insert query + making the function deterministic and pragma UDF. The second option is ~30 minute faster as compared to the first one. And yes this insert query executes many times in that process so ~30minutes difference is for total number of executions.

On Sat, Oct 30, 2021 at 4:57 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi all,
>
> Just want to say that making a function deterministic gives the same
> effect of reducing number of function calls without rewriting a query.
>
>
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning expert
> Oracle Database Developer Choice Award winner
> Oracle ACE Associate
> http://orasql.org
>
> сб, 30 окт. 2021 г., 13:12 Mohamed Houri <mohamed.houri_at_gmail.com>:
>
>> I only want to support what Sayan has said about the result cache that
>> can cause *Result Cache: RC Latch* when the result of the function call
>> is invalidated for one reason or another. Here’s below a practical case of
>> the wrong usage of the *result_cache hint* which can give you an idea
>> about its effect when you would use it in your function or cache the result
>> of your function call.
>>
>> https://hourim.wordpress.com/2018/08/17/wrong-utilisation-of-result-cache/
>>
>> As for the idea of surrounding the call to your function with a *select
>> from dual*, I think that's a good idea. Here is another example taken
>> from a real-life running system that shows the benefit of Scalar Subquery
>> Caching
>>
>>
>> https://hourim.wordpress.com/2019/12/18/scalar-subquery-caching-the-select-from-dual-trick/
>>
>> Best regards
>>
>> Mohamed Houri
>>
>>
>>
>>
>>
>> Le sam. 30 oct. 2021 à 04:58, Lok P <loknath.73_at_gmail.com> a écrit :
>>
>>> Thank You So much Sayan and Jonathan.
>>>
>>> Along with the to_char changes. I will also try to see the performance
>>> of one test case with a combination of both i.e. SSC in the main
>>> query(i.e. wrapping fun1 in select..from dual) And deterministic function
>>> and pragma UDF in the function code. Will try to see if it's giving the
>>> best of both features.
>>>
>>> On Sat, Oct 30, 2021 at 3:05 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
>>> wrote:
>>>
>>>> To add to Sayan's comment about eliminating the select from dual:
>>>> you're also wasting CPU converting between date and character - sticking
>>>> with date arithmetic is likely to be more efficient (and arguably easier to
>>>> understand):
>>>>
>>>> IF to_char(INPDT, 'HH:MI:SS AM') = '12:00:00 AM' then
>>>> SELECT TO_DATE(TO_CHAR(INPDT,'MM/DD/YYYY') || ' ' ||
>>>> TO_CHAR(IN_STDT, 'HH:MI:SS AM'), 'MM/DD/YYYY HH:MI:SS AM') INTO o_pdt FROM
>>>> DUAL;
>>>> ELSE
>>>> o_pdt := INPDT;
>>>> END IF;
>>>>
>>>> --
>>>>
>>>> if inpdt = trunc(inpdt) then
>>>> o_pdt := inpdt + (in_stst - trunc(in_stdt));
>>>> else
>>>> o_pdt := inpdt;
>>>> end if;
>>>>
>>>>
>>>> You might note that another option to reduce PL/SQL overheads is to use
>>>> the "with function" feature that appeared in 12c
>>>> (see, for example,
>>>> https://jonathanlewis.wordpress.com/2013/06/30/12c-fun/ ) Though if
>>>> your function is used in many different SQL statements you might still want
>>>> to keep it as a standalone PL/SQL function declared with pragma UDF.
>>>>
>>>> Determinism vs. scalar subquery caching - the scalar subquery cache is
>>>> fairly limited in size, and you can be unlucky with hash collisions; so
>>>> your best bet is to follow Sayan's advice to start with; but it shouldn't
>>>> take much effort to test both options.
>>>>
>>>>
>>>> Regards
>>>> Jonathan Lewis
>>>>
>>>>
>>>>
>>>> On Fri, 29 Oct 2021 at 20:44, Lok P <loknath.73_at_gmail.com> wrote:
>>>>
>>>>> Hello Listers, We have below Insert query which is executed ~50k - 60K
>>>>> times each day. And this is consuming major amount of time in a specific
>>>>> process/procedure which in turns gets called from multiple scenarios. And
>>>>> while looking into the sql monitor we saw its majorly CPU time and its in
>>>>> the loading part only(~350 secs), which seems to be coming from the
>>>>> function call in the SELECT part of the insert query. As shown in the sql
>>>>> monitor this function seems to be getting called atleast ~8million times
>>>>> here one time for each row for this single execution of the main insert
>>>>> query. I have posted the function code here below. So in total this
>>>>> function call seems to be happening billions of time in a day. Its oracle
>>>>> database version 19.9.0.0.0. I never used but heard of few option in such
>>>>> cases like scalar subquery caching, Result cache, deterministic function
>>>>> etc. So wanted to understand, if wrapping up this function call in a
>>>>> "select from dual" i.e utilizing scalar subquery caching technique will
>>>>> help us here? Or as the function body doesn't depend on any DB tables etc,
>>>>> a results cache should be opted here? What should be the best option here?
>>>>>
>>>>> We have currently having below result cache option as i see in
>>>>> v$parameter.
>>>>>
>>>>> NAME VALUE
>>>>> result_cache_mode MANUAL
>>>>> result_cache_max_size 107380736
>>>>> result_cache_max_result 5
>>>>> result_cache_remote_expiration 0
>>>>> client_result_cache_size 0
>>>>> client_result_cache_lag 3000
>>>>>
>>>>>
>>>>> *Main query:-*
>>>>> INSERT INTO GLBL_TMP_TBL1 (.........)
>>>>> SELECT............
>>>>> pkg.fun1 (RFFT.AMT, RTFX.STS, RTFX.PDT, :B1, ( :B1 +
>>>>> 1), 'X'),
>>>>> pkg.fun1 (RFFT.AMT, RTFX.STS, RTFX.PDT, :B1, ( :B1 + 1),
>>>>> 'Y'),
>>>>> TRUNC ( :B1)....
>>>>> FROM RTFX RTFX, RFFT RFFT, ND ND, RDC RDC
>>>>> WHERE RTFX.FFXID = RFFT.FXID
>>>>> AND RTFX.AC_Key = RFFT.C_Key
>>>>> AND RFFT.CT_Key = ND.N_Key
>>>>> AND ND.N_ETY IN ('XX', 'YY', 'ZZ')...;
>>>>>
>>>>> *Function code:- *
>>>>>
>>>>> FUNCTION fun1(INAMT IN NUMBER, INSTS IN VARCHAR2, INPDT IN
>>>>> DATE,IN_STDT IN DATE, IN_ENDT IN DATE, INBK IN VARCHAR2) RETURN
>>>>> NUMBER IS
>>>>> o_pdt DATE;
>>>>> BEGIN
>>>>> IF INBK = 'Y' THEN
>>>>> IF (INSTS = 'AA' AND INPDT > IN_ENDT) THEN
>>>>> RETURN INAMT ;
>>>>> END IF;
>>>>> IF (INSTS IN ('SS','BB', 'TT','ZZ')) THEN
>>>>> RETURN INAMT ;
>>>>> END IF;
>>>>> ELSIF (INBK = 'X')
>>>>> THEN
>>>>> IF to_char(INPDT, 'HH:MI:SS AM') = '12:00:00 AM' then
>>>>> SELECT TO_DATE(TO_CHAR(INPDT,'MM/DD/YYYY') || ' ' ||
>>>>> TO_CHAR(IN_STDT, 'HH:MI:SS AM'), 'MM/DD/YYYY HH:MI:SS AM') INTO o_pdt FROM
>>>>> DUAL;
>>>>> ELSE
>>>>> o_pdt := INPDT;
>>>>> END IF;
>>>>> IF (INSTS = 'AA' AND o_pdt >= IN_STDT AND o_pdt < IN_ENDT)
>>>>> THEN
>>>>> DBMS_OUTPUT.PUT_LINE(o_pdt || ' - ' || INAMT);
>>>>> RETURN INAMT ;
>>>>> END IF;
>>>>> END IF;
>>>>> RETURN 0;
>>>>> END;
>>>>> /
>>>>>
>>>>> Global Information
>>>>>
>>>>> ------------------------------
>>>>> STATUS : DONE
>>>>> Instance ID : 4
>>>>> SQL Execution ID : 67303461
>>>>> Execution Started : 10/29/2021 07:03:58
>>>>> First Refresh Time : 10/29/2021 07:04:02
>>>>> Last Refresh Time : 10/29/2021 07:16:19
>>>>> Duration : 741s
>>>>>
>>>>> Global Stats
>>>>>
>>>>> =======================================================================================================
>>>>> | Elapsed | Cpu | IO | Concurrency | Cluster | PL/SQL | Other |
>>>>> Buffer | Read | Read |
>>>>> | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) |
>>>>> Waits(s) | Gets | Reqs | Bytes |
>>>>>
>>>>> =======================================================================================================
>>>>> | 742 | 704 | 35 | 0.17 | 0.00 | 157 | 3.07
>>>>> | 11M | 67661 | 529MB |
>>>>>
>>>>> =======================================================================================================
>>>>>
>>>>> SQL Plan Monitoring Details (Plan Hash Value=3120541595)
>>>>>
>>>>>
>>>>> ======================================================================================================================================================================================================================
>>>>>
>>>>> | Id | Operation |
>>>>> Name | Rows | Cost | Time | Start | Execs | Rows | Read
>>>>> | Read | Activity | Activity Detail |
>>>>>
>>>>> | | |
>>>>> | (Estim) | | Active(s) | Active | | (Actual) |
>>>>> Reqs | Bytes | (%) | (# samples) |
>>>>>
>>>>>
>>>>> ======================================================================================================================================================================================================================
>>>>>
>>>>> | 0 | INSERT STATEMENT |
>>>>> | | | 738 | +4 | 1 | 0 | |
>>>>> | 20.82 | Cpu (152) |
>>>>>
>>>>> | 1 | LOAD TABLE CONVENTIONAL | GLBL_TMP_TBL1
>>>>> | | | 741 | +1 | 1 | 0 | |
>>>>> | 32.33 | Cpu (236) |
>>>>>
>>>>> | 2 | NESTED LOOPS |
>>>>> | 115 | 86999 | 738 | +4 | 1 | 8M |
>>>>> | | 0.27 | Cpu (2) |
>>>>>
>>>>> | 3 | NESTED LOOPS |
>>>>> | 115 | 86999 | 738 | +4 | 1 | 8M |
>>>>> | | | |
>>>>>
>>>>> | 4 | NESTED LOOPS |
>>>>> | 115 | 86884 | 738 | +4 | 1 | 8M |
>>>>> | | 0.27 | Cpu (2) |
>>>>>
>>>>> | 5 | NESTED LOOPS |
>>>>> | 4405 | 82363 | 738 | +4 | 1 | 8M |
>>>>> | | | |
>>>>>
>>>>> | 6 | TABLE ACCESS STORAGE FULL | RTFX
>>>>> | 3291 | 14 | 738 | +4 | 1 | 4250 | |
>>>>> | | |
>>>>>
>>>>> | 7 | PARTITION LIST ITERATOR |
>>>>> | 1 | 25 | 738 | +4 | 4250 | 8M | |
>>>>> | | |
>>>>>
>>>>> | 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | RFFT
>>>>> | 1 | 25 | 738 | +4 | 4249 | 8M | 55950
>>>>> | 437MB | 5.48 | Cpu (11) |
>>>>>
>>>>> | | |
>>>>> | | | | | | | |
>>>>> | | cell single block physical read (29) |
>>>>>
>>>>> | 9 | INDEX RANGE SCAN | RFFT_IX1
>>>>> | 836 | 4 | 738 | +4 | 4249 | 8M | 9331 |
>>>>> 73MB | 1.37 | Cpu (5) |
>>>>>
>>>>> | | |
>>>>> | | | | | | | |
>>>>> | | cell single block physical read (5) |
>>>>>
>>>>> | 10 | TABLE ACCESS BY INDEX ROWID BATCHED | ND
>>>>> | 1 | 2 | 738 | +4 | 8M | 8M |
>>>>> | | 3.15 | Cpu (23) |
>>>>>
>>>>> | 11 | INDEX RANGE SCAN | ND_IX6
>>>>> | 1 | 1 | 738 | +4 | 8M | 8M |
>>>>> | | 2.74 | Cpu (20) |
>>>>>
>>>>> | 12 | INDEX UNIQUE SCAN | RDC_PK
>>>>> | 1 | | 742 | +0 | 8M | 8M |
>>>>> | | 0.55 | Cpu (4) |
>>>>>
>>>>> | 13 | TABLE ACCESS BY INDEX ROWID | RDC
>>>>> | 1 | 1 | 738 | +4 | 8M | 8M | |
>>>>> | 3.15 | Cpu (23) |
>>>>>
>>>>>
>>>>> ======================================================================================================================================================================================================================
>>>>>
>>>>
>>
>> --
>>
>> 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>
>>
>>

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