Re: Should we use Subquery caching or result cache here

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