I just fetched sql monitor of one of the new executions of same query,
with the modified code in production i.e. having scalar subquery caching
implemented for both the function calls in the Insert as select query,
along with the change in function definition to deterministic pragma UDF +
replaced the Select to_char.. from dual statement with the simple
assignment operator in the function code. Though the below execution is
not exactly doing the same amount of work as I had posted one sql monitor
at the start of this thread, btw, in this execution also ~70% activity is
contributed by the insert itself and almost all are on CPU only.
Though we have removed the querying of Dual table from within the function
code, But we added two new calls to Dual table while converting it into
scalar subquery caching in the main query i.e.. 'select fun1().. from
dual', so is it possible that this change must be adding some
additional cpu overhead here? And we should try removing the scalar
subquery caching part as now the function is declared as deterministic?
Global Information
------------------------------
Status : DONE
Instance ID : 2
SQL Execution ID : 33679734
Execution Started : 11/01/2021 15:08:25
First Refresh Time : 11/01/2021 15:08:32
Last Refresh Time : 11/01/2021 15:09:25
Duration : 60s
Global Stats
=============================================================================
| Elapsed | Cpu | IO | Cluster | PL/SQL | Buffer | Read | Read
|
| Time(s) | Time(s) | Waits(s) | Waits(s) | Time(s) | Gets | Reqs | Bytes
|
=============================================================================
| 60 | 56 | 3.39 | 0.08 | 17 | 2M | 8650 | 68MB
|
=============================================================================
SQL Plan Monitoring Details (Plan Hash Value=1542410067)
===================================================================================================================================================================================================================
| 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 |
| | | 54 | +7 | 1 | 0 |
| | | |
| 1 | LOAD TABLE CONVENTIONAL | GLBL_TMP_TBL1
| | | 60 | +1 | 1 | 0 |
2 | 16384 | 73.33 | Cpu (44) |
| 2 | FAST DUAL |
| 1 | 2 | 54 | +7 | 970K | 970K |
| | | |
| 3 | FAST DUAL |
| 1 | 2 | 54 | +7 | 970K | 970K |
| | | |
| 4 | NESTED LOOPS |
| 1 | 3919 | 54 | +7 | 1 | 2M |
| | | |
| 5 | NESTED LOOPS |
| 1 | 3919 | 54 | +7 | 1 | 2M |
| | | |
| 6 | NESTED LOOPS |
| 1 | 3918 | 54 | +7 | 1 | 2M |
| | | |
| 7 | NESTED LOOPS |
| 186 | 3731 | 54 | +7 | 1 | 2M |
| | | |
| 8 | TABLE ACCESS STORAGE FULL | RTFX
| 138 | 2 | 54 | +7 | 1 | 207 |
| | | |
| 9 | PARTITION LIST ITERATOR |
| 1 | 27 | 54 | +7 | 207 | 2M |
| | | |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | RFFT
| 1 | 27 | 55 | +6 | 207 | 2M |
7745 | 61MB | 8.33 | Cpu (1) |
| | |
| | | | | | |
| | | cell single block physical read (4) |
| 11 | INDEX RANGE SCAN | RFFT_IX1
| 841 | 4 | 54 | +7 | 207 | 2M |
903 | 7MB | | |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED | ND
| 1 | 2 | 54 | +7 | 2M | 2M |
| | 3.33 | Cpu (2) |
| 13 | INDEX RANGE SCAN | ND_IX6
| 1 | 1 | 54 | +7 | 2M | 2M |
| | 1.67 | Cpu (1) |
| 14 | INDEX UNIQUE SCAN | RDC_PK
| 1 | | 54 | +7 | 2M | 2M |
| | 3.33 | Cpu (2) |
| 15 | TABLE ACCESS BY INDEX ROWID | RDC
| 1 | 1 | 54 | +7 | 2M | 2M |
| | 10.00 | Cpu (6) |
===================================================================================================================================================================================================================
On Mon, Nov 1, 2021 at 4:02 PM Lok P <loknath.73@xxxxxxxxx> wrote:
Thank you All. It helped.
Didn't get a chance to test all the options as it's a third party app. But
as we tested two options i.e. 1) with only scalar subquery caching on the
Insert as select query and no other changes to function 2) Replacing the
'select from.. dual' query inside the function with just simple
assignment operator + Scalar subquery caching on the insert query + making
the function deterministic and pragma UDF. The second option is ~30 minute
faster as compared to the first one. And yes this insert query executes
many times in that process so ~30minutes difference is for total number of
executions.
On Sat, Oct 30, 2021 at 4:57 PM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
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@xxxxxxxxx>:
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@xxxxxxxxx> 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@xxxxxxxxx>
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@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) |
======================================================================================================================================================================================================================
--
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>
