Re: Should we use Subquery caching or result cache here

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