Deterministic function vs scalar subquery caching. Part 1

Posted on by Posted in deterministic functions, oracle, scalar subquery caching 8,571 Page views 8 Comments

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

[sourcecode language=”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;
/
[/sourcecode]

[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

[sourcecode language=”sql”]
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
[/sourcecode]

[collapse]
We see that with p=75 function was called twice! Now we can execute function with parameters in different order – first call will be with 75:
75,1-100,1-100

[sourcecode language=”sql”]
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.
[/sourcecode]

[collapse]

Now we see that istead of 75, function was called with p=48. Trying now only these two values:
75,48,75,48

[sourcecode language=”sql”]
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.
[/sourcecode]

[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

[sourcecode language=”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
[/sourcecode]

[collapse]

dtrm_arraysize_test.sql

[sourcecode language=”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
[/sourcecode]

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

[sourcecode language=”sql”]
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
[/sourcecode]

[collapse]

As you see it is always have same executions count. Now for deterministic function:
Spoiler

[sourcecode language=”sql”]
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
[/sourcecode]

[collapse]
Now you see that there are no unnecessary executions when arraysize equal or more that out 75 different parameters, and when arraysize=100, we see that only last 50 parameters was executed twice!

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

[sourcecode language=”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
[/sourcecode]

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

[sourcecode language=”sql”]
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.
[/sourcecode]

[collapse]

UPD: Part 2

Page views: 8,571