Re: Should we use Subquery caching or result cache here

In fact, for "insert select function(...) from..." both - scalar subquery caching and deterministic functions caching work almost similarly: http://orasql.org/2013/03/13/deterministic-function-vs-scalar-subquery-caching-part-3/ and cases when SSC is better (for example p.4 or p.5 from the article above) are pretty rare. So usually "deterministic" and "pragma UDF" is enough.
You can check it - it should give good results. Re "result cache": we need to know more details about a number of distinct sets of the function arguments: how many different input arguments do you have, how often do they change, etc..
For example, I see that you have some dates as input arguments - don't they always change? Do you really need to cache results for arguments that will never be requested?
Moreover, there is a chance that you will get a problem with RC latches, since RC is global for all sessions within a database instance. I would suggest also scalar SQL Macros instead of your function, but it's too new and requires Oracle 21, it would be something like this: create or replace
FUNCTION fun1(INAMT IN NUMBER, INSTS IN VARCHAR2, INPDT IN DATE, IN_STDT IN DATE, IN_ENDT IN DATE, INBK IN VARCHAR2)   RETURN NUMBER sql_macro(scalar)
is
begin
  return q'{
    case

        when
         (INBK = 'Y' and ((INSTS = 'AA' AND INPDT > IN_ENDT) or (INSTS IN
('SS','BB', 'TT','ZZ'))) )
         or
         (INBK = 'X' and INSTS = 'AA'
          and /*o_pdt*/ case when INPDT=trunc(INPDT) then
INPDT+(IN_STDT-trunc(IN_STDT)) else INPDT end  >= IN_STDT
          and /*o_pdt*/ case when INPDT=trunc(INPDT) then
INPDT+(IN_STDT-trunc(IN_STDT)) else INPDT end  < IN_ENDT
          )
        then INAMT
        else 0

On Sat, Oct 30, 2021 at 12:09 AM Lok P <loknath.73_at_gmail.com> wrote:

> 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
>>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org


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