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
end
}';
end;
/
On Sat, Oct 30, 2021 at 12:09 AM Lok P <loknath.73@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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
