Oracle SQL

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.(About hash tables and hash collisions for scalar subquery caching excelent wrote 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

Spoiler:: Content of deterministic_ssc_ddl.sql	Select Show
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; /

Spoiler:: Content of deterministic_ssc_ddl.sql

Select Show

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;
/

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:

Spoiler:: 1-100,1-100	Select Show
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

Spoiler:: 1-100,1-100

Select Show

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

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:

Spoiler:: 75,1-100,1-100	Select Show
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.

Spoiler:: 75,1-100,1-100

Select Show

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.

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

Spoiler:: 75,48,75,48	Select Show
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.

Spoiler:: 75,48,75,48

Select Show

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.

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

Spoiler:: ssc_arraysize_test.sql	Select Show
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

Spoiler:: ssc_arraysize_test.sql

Select Show

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

Spoiler:: dtrm_arraysize_test.sql	Select Show
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

Spoiler:: dtrm_arraysize_test.sql

Select Show

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

These scripts will show that deterministic functions results cached only within one fetch call, and ssc mechanizm does not depend on fetch size.

Spoiler:: Output for ssc:	Select Show
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

Spoiler:: Output for ssc:

Select Show

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

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

Spoiler:: Hidden text	Select Show
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

Spoiler:: Hidden text

Select Show

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

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

Spoiler:: Contents of deterministic_ssc_test.sql	Select Show
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

Spoiler:: Contents of deterministic_ssc_test.sql

Select Show

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

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

SelectShow colspan='2' style=''>

UPD: Part 2