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]

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]

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]

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: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:Now we see that istead of 75, function was called with p=48. Trying now only these two values: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]

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]

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]

These scripts will show that deterministic functions results cached only within one fetch call, and ssc mechanizm does not depend on fetch size.As you see it is always have same executions count. Now for deterministic function: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 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]

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

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:

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