Re: Should we use Subquery caching or result cache here

Thank You Sayan.

I got your point regarding hitting the dual function and adding overhead here so it should be removed at first. But , do you mean to say that adding deterministic + pragma UDF like below , to the function should be enough and we will not need the scalar subquery caching here? Can you please elaborate a bit more about these two , why you think this will suffice and we don't need result cache and/or scalar subquery caching etc?

FUNCTION fun1(INAMT IN NUMBER, INSTS IN VARCHAR2, INPDT IN DATE,IN_STDT IN DATE, IN_ENDT IN DATE, INBK IN VARCHAR2) RETURN NUMBER *DETERMINISTIC IS Pragma UDF*
 o_pdt DATE;
BEGIN
........

On Sat, Oct 30, 2021 at 2:02 AM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi Lok,
>
> First of all I would replace
> 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;
> to
> o_pdt := TO_DATE(TO_CHAR(INPDT,'MM/DD/YYYY') || ' ' || TO_CHAR(IN_STDT,
> 'HH:MI:SS AM'), 'MM/DD/YYYY HH:MI:SS AM');
> and remove dbms_output.put_line. After this, this function will become a
> simple deterministic function without SQL queries, so I would add
> 'deterministic' and 'pragma UDF;'.
>
>
> On Fri, Oct 29, 2021 at 10:43 PM 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) |
>>
>>
>> ======================================================================================================================================================================================================================
>>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

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