Re: Should we use Subquery caching or result cache here

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