I recently did a comparison caching mechanisms of scalar subquery caching(SSC) and deterministic functions in 11.2. Unfortunately, I do not have enough time to do a full analysis, so I will post it in parts.
Today’s topics:
1. Both mechanisms are based on hash functions.(You can read great articles about hash tables and hash collisions for scalar subquery caching by Tom Kyte and Jonathan Lewis(“Cost-Based Oracle fundamentals” chapter 9))
2. Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
3. Hash collisions depends on the single parameter “_query_execution_cache_max_size” for both mechanizms, but they are more frequent in SSC.
UPD: Part 2
First of all, execute script: deterministic_ssc_ddl.sql
Content of deterministic_ssc_ddl.sql
drop table t_unique purge;
drop table t_params purge;
drop function f_ssc;
drop function f_deterministic;
---------------
create table t_unique(i int);
create table t_params(exec_n int,p int);
create index ix_t_params_p on t_params(p);
alter session set optimizer_dynamic_sampling=0;
--- function for counting deterministic function executions:
create or replace function f_deterministic(p int)
return int deterministic as
procedure log_it is
pragma autonomous_transaction;
begin
dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
insert into t_params
values(userenv('client_info'),p);
commit;
end;
begin
log_it;
return 1;
end;
/
--- function for counting scalar subquery executions:
create or replace function f_ssc(a int)
return int as
procedure log_it is
pragma autonomous_transaction;
begin
dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
insert into t_params
values(userenv('client_info'),a);
commit;
end;
begin
log_it;
return 1;
end;
/
[collapse]
It creates necessary functions and tables for tests. Main test functions are f_deterministic and f_ssc.
These function logs executions into table t_params, so we can simply see with which parameters functions was called twice and more.
Simple example:
1-100,1-100
SQL> truncate table t_params;
Table truncated.
Elapsed: 00:00:00.07
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('1 to 100, 1 to 100'
4 columns n int path '.'
5 );
FD
----------
200
1 row selected.
Elapsed: 00:00:00.04
SQL> select p,count(*) from t_params group by p having count(*)>1 order by p;
P COUNT(*)
---------- ----------
75 2
84 2
87 2
89 2
93 2
96 2
6 rows selected.
Elapsed: 00:00:00.01
[collapse]
75,1-100,1-100
SQL> truncate table t_params;
Table truncated.
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('75,1 to 100,1 to 100'
4 columns n int path '.'
5 );
FD
----------
201
1 row selected.
SQL> select p,count(*) from t_params group by p having count(*)>1 order by p;
P COUNT(*)
---------- ----------
48 2
84 2
87 2
89 2
93 2
96 2
6 rows selected.
[collapse]
Now we see that istead of 75, function was called with p=48. Trying now only these two values:
75,48,75,48
SQL> truncate table t_params;
Table truncated.
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('75,48,75,48'
4 columns n int path '.'
5 );
FD
----------
4
1 row selected.
SQL> select p,count(*) from t_params group by p having count(*)>1 order by p;
P COUNT(*)
---------- ----------
48 2
1 row selected.
[collapse]
So we see that there is a hash collision between 48 and 75!
Tests with different fetch sizes
Execute now scripts ssc_arraysize_test.sql and dtrm_arraysize_test.sql
ssc_arraysize_test.sql
spool spools/ssc_arraysize.sql
-----------
set arraysize 15 term off;
truncate table t_params;
select (select 1 from dual where f_ssc(n)=1) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
-----------
set arraysize 75 term off;
truncate table t_params;
select (select 1 from dual where f_ssc(n)=1) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
-----------
set arraysize 100 term off;
truncate table t_params;
select (select 1 from dual where f_ssc(n)=1) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
-----------
set arraysize 150 term off;
truncate table t_params;
select (select 1 from dual where f_ssc(n)=1) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
spool off
[collapse]
dtrm_arraysize_test.sql
spool spools/dtrm_arraysize.sql
-----------
set arraysize 15 term off;
truncate table t_params;
select f_deterministic(n) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
-----------
set arraysize 75 term off;
truncate table t_params;
select f_deterministic(n) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
-----------
set arraysize 100 term off;
truncate table t_params;
select f_deterministic(n) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
-----------
set arraysize 150 term off;
truncate table t_params;
select f_deterministic(n) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
spool off
[collapse]
These scripts will show that deterministic functions results cached only within one fetch call, and ssc mechanizm does not depend on fetch size.
Output for ssc:
SQL> @test1/ssc_arraysize_test
SQL> spool spools/ssc_arraysize.sql
SQL> -----------
SQL> set arraysize 15 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
28
1 row selected.
Elapsed: 00:00:00.00
SQL> -----------
SQL> set arraysize 75 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
28
1 row selected.
Elapsed: 00:00:00.00
SQL> -----------
SQL> set arraysize 100 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
28
1 row selected.
Elapsed: 00:00:00.00
SQL> -----------
SQL> set arraysize 150 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
28
1 row selected.
Elapsed: 00:00:00.00
SQL> spool off
[collapse]
As you see it is always have same executions count. Now for deterministic function:
Spoiler
SQL> @test1/dtrm_arraysize_test
SQL> spool spools/dtrm_arraysize.sql
SQL> -----------
SQL> set arraysize 15 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
75
1 row selected.
Elapsed: 00:00:00.00
SQL> -----------
SQL> set arraysize 75 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
75
1 row selected.
Elapsed: 00:00:00.00
SQL> -----------
SQL> set arraysize 100 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
50
1 row selected.
Elapsed: 00:00:00.00
SQL> -----------
SQL> set arraysize 150 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
1
1 row selected.
Elapsed: 00:00:00.00
SQL> spool off
[collapse]
3. Now hash collisions counting
Download script deterministic_ssc_test.sql
and execute with these parameters(first parameter is the value for setting parameter “_query_execution_cache_max_size”, and second – number of different parameters, with which functions will be called):
@test1/deterministic_ssc_test.sql 65536 80 @test1/deterministic_ssc_test.sql 65536 100 @test1/deterministic_ssc_test.sql 131072 80 @test1/deterministic_ssc_test.sql 131072 100 @test1/deterministic_ssc_test.sql 262144 80 @test1/deterministic_ssc_test.sql 262144 100
Contents of deterministic_ssc_test.sql
spool spools/deterministic_ssc_&2-&1.sql
alter session set "_query_execution_cache_max_size" = &1;
col postfix new_val postfix noprint
select '&2'||'_'||'&1' postfix from dual;
---- Test for 1-&2,1-&2 - SSC
truncate table t_params;
exec dbms_application_info.set_client_info(0);
select
sum((select 1 from dual where f_ssc(n)=1)) ssc
from
xmltable('1 to &2, 1 to &2'
columns n int path '.'
);
create table t_params_ssc_&postfix as select * from t_params;
---- Test for 1-&2,1-&2 - Deterministic
truncate table t_params;
exec dbms_application_info.set_client_info(0);
select
sum(f_deterministic(n)) fd
from
xmltable('1 to &2, 1 to &2'
columns n int path '.'
);
create table t_params_dtrm_&postfix as select * from t_params;
spool off
[collapse]
Script will call functions with parameter from 1 to second parameter(80 and 100) twice for each value of “_query_execution_cache_max_size”.
After it execute query:
select
80 "Different parameters"
,(select count(count(*)) cnt_scc_65536 from t_params_ssc_80_65536 group by p having count(*)>1) cnt_scc_65536
,(select count(count(*)) cnt_scc_131072 from t_params_ssc_80_131072 group by p having count(*)>1) cnt_scc_131072
,(select count(count(*)) cnt_scc_262144 from t_params_ssc_80_262144 group by p having count(*)>1) cnt_scc_262144
,(select count(count(*)) cnt_dtrm_65536 from t_params_dtrm_80_65536 group by p having count(*)>1) cnt_dtrm_65536
,(select count(count(*)) cnt_dtrm_131072 from t_params_dtrm_80_131072 group by p having count(*)>1) cnt_dtrm_131072
,(select count(count(*)) cnt_dtrm_262144 from t_params_dtrm_80_262144 group by p having count(*)>1) cnt_dtrm_262144
from dual
union all
select
100 p
,(select count(count(*)) cnt_scc_65536 from t_params_ssc_100_65536 group by p having count(*)>1) cnt_scc_65536
,(select count(count(*)) cnt_scc_131072 from t_params_ssc_100_131072 group by p having count(*)>1) cnt_scc_131072
,(select count(count(*)) cnt_scc_262144 from t_params_ssc_100_262144 group by p having count(*)>1) cnt_scc_262144
,(select count(count(*)) cnt_dtrm_65536 from t_params_dtrm_100_65536 group by p having count(*)>1) cnt_dtrm_65536
,(select count(count(*)) cnt_dtrm_131072 from t_params_dtrm_100_131072 group by p having count(*)>1) cnt_dtrm_131072
,(select count(count(*)) cnt_dtrm_262144 from t_params_dtrm_100_262144 group by p having count(*)>1) cnt_dtrm_262144
from dual;
Different parameters CNT_SCC_65536 CNT_SCC_131072 CNT_SCC_262144 CNT_DTRM_65536 CNT_DTRM_131072 CNT_DTRM_262144
-------------------- ------------- -------------- -------------- -------------- --------------- ---------------
80 64 49 33 1 1 0
100 84 69 48 6 4 0
This query shows how many unnecessary executions were, and how much they reduced with increasing _query_execution_cache_max_size.
Full comparison result:
SQL> with
2 ssc_80_65536 as (select p,count(*) cnt_scc_65536 from t_params_ssc_100_65536 group by p)
3 ,ssc_80_131072 as (select p,count(*) cnt_scc_131072 from t_params_ssc_100_131072 group by p)
4 ,ssc_80_262144 as (select p,count(*) cnt_scc_262144 from t_params_ssc_100_262144 group by p)
5 ,dtrm_80_65536 as (select p,count(*) cnt_dtrm_65536 from t_params_dtrm_100_65536 group by p)
6 ,dtrm_80_131072 as (select p,count(*) cnt_dtrm_131072 from t_params_dtrm_100_131072 group by p)
7 ,dtrm_80_262144 as (select p,count(*) cnt_dtrm_262144 from t_params_dtrm_100_262144 group by p)
8 select
9 t1.p
10 ,cnt_scc_65536
11 ,cnt_scc_131072
12 ,cnt_scc_262144
13 ,cnt_dtrm_65536
14 ,cnt_dtrm_131072
15 ,cnt_dtrm_262144
16 from ssc_80_65536 t1
17 ,ssc_80_131072 t2
18 ,ssc_80_262144 t3
19 ,dtrm_80_65536 t4
20 ,dtrm_80_131072 t5
21 ,dtrm_80_262144 t6
22 where
23 t1.p=t2.p
24 and t1.p=t3.p
25 and t1.p=t4.p
26 and t1.p=t5.p
27 and t1.p=t6.p
28 order by 1
29 /
P CNT_SCC_65536 CNT_SCC_131072 CNT_SCC_262144 CNT_DTRM_65536 CNT_DTRM_131072 CNT_DTRM_262144
---------- ------------- -------------- -------------- -------------- --------------- ---------------
1 1 1 1 1 1 1
2 1 1 1 1 1 1
3 1 1 1 1 1 1
4 1 1 1 1 1 1
5 1 1 1 1 1 1
6 1 1 1 1 1 1
7 1 1 1 1 1 1
8 1 1 1 1 1 1
9 1 1 1 1 1 1
10 1 1 1 1 1 1
11 1 1 1 1 1 1
12 2 2 2 1 1 1
13 2 1 1 1 1 1
14 2 1 1 1 1 1
15 2 2 2 1 1 1
16 1 1 1 1 1 1
17 1 1 1 1 1 1
18 2 2 2 1 1 1
19 1 1 1 1 1 1
20 2 1 1 1 1 1
21 2 1 1 1 1 1
22 2 1 1 1 1 1
23 2 2 2 1 1 1
24 2 2 1 1 1 1
25 2 1 1 1 1 1
26 1 1 1 1 1 1
27 1 1 1 1 1 1
28 2 2 1 1 1 1
29 2 2 2 1 1 1
30 2 2 1 1 1 1
31 2 2 2 1 1 1
32 2 2 1 1 1 1
33 2 2 2 1 1 1
34 2 2 1 1 1 1
35 2 1 1 1 1 1
36 2 2 1 1 1 1
37 2 1 1 1 1 1
38 2 2 2 1 1 1
39 2 1 1 1 1 1
40 2 2 2 1 1 1
41 2 1 1 1 1 1
42 2 2 2 1 1 1
43 2 2 2 1 1 1
44 2 2 1 1 1 1
45 2 2 1 1 1 1
46 2 2 2 1 1 1
47 2 1 1 1 1 1
48 2 2 2 1 1 1
49 2 2 2 1 1 1
50 2 2 2 1 1 1
51 2 2 2 1 1 1
52 2 2 1 1 1 1
53 2 2 1 1 1 1
54 2 2 2 1 1 1
55 2 1 1 1 1 1
56 2 2 1 1 1 1
57 2 2 2 1 1 1
58 2 2 1 1 1 1
59 2 2 1 1 1 1
60 2 2 2 1 1 1
61 2 2 2 1 1 1
62 2 1 1 1 1 1
63 2 2 2 1 1 1
64 2 2 2 1 1 1
65 2 2 2 1 1 1
66 2 1 1 1 1 1
67 2 2 2 1 1 1
68 2 2 1 1 1 1
69 2 2 1 1 1 1
70 2 2 2 1 1 1
71 2 2 2 1 1 1
72 2 2 1 1 1 1
73 2 2 2 1 1 1
74 2 2 2 1 1 1
75 2 1 1 2 2 1
76 2 2 2 1 1 1
77 2 2 2 1 1 1
78 2 2 2 1 1 1
79 2 2 2 1 1 1
80 2 2 2 1 1 1
81 2 2 2 1 1 1
82 2 2 2 1 1 1
83 2 2 2 1 1 1
84 2 2 2 2 2 1
85 2 2 2 1 1 1
86 2 2 2 1 1 1
87 2 2 2 2 1 1
88 2 2 1 1 1 1
89 2 2 2 2 2 1
90 2 2 2 1 1 1
91 2 2 2 1 1 1
92 2 2 1 1 1 1
93 2 2 2 2 1 1
94 2 2 1 1 1 1
95 2 2 2 1 1 1
96 2 2 1 2 2 1
97 2 2 2 1 1 1
98 2 2 2 1 1 1
99 2 2 1 1 1 1
100 2 2 2 1 1 1
100 rows selected.
[collapse]
UPD: Part 2