In previous part i already point out that:
- Both mechanisms are based on hash functions.
- Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
- Hash collisions depends on the single parameter “_query_execution_cache_max_size” for both mechanizms, but they are more frequent in SSC.
Today’s topic:
4. Deterministic functions does not keeps last result as scalar subquery caching
5. Caching of deterministic functions results turns off after a certain number of attempts to get the value from the cache. But SSC always returns results from cache if values already cached.
As Tom Kyte wrote, Oracle keeps last scalar subquery result even if it cannot be saved in hash tables because of hash collision, but deteministic functions caching mechanism doesn’t.
Let’s do a test on values with hash collision, which i found in previous part – 48 and 75.
SQL> truncate table t_params;
Table truncated.
-- first query shows that each call with 75 bypasses cache:
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('48,75,48,75,48,75'
4 columns n int path '.'
5 );
FD
----------
6
1 row selected.
SQL> select p,count(*) cnt
2 from t_params
3 group by p;
P CNT
---------- ----------
48 1
75 3
2 rows selected.
SQL> truncate table t_params;
Table truncated.
-- now will do it in sequence
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('48,75,75,75'
4 columns n int path '.'
5 );
FD
----------
4
1 row selected.
SQL> select p,count(*) cnt
2 from t_params
3 group by p;
P CNT
---------- ----------
48 1
75 3
2 rows selected.
As you see, the count of execution stays the same, unlike as with scalar subquery caching:
SQL> truncate table t_params;
Table truncated.
SQL> select sum((select 1 from dual where f_ssc(n)>0)) fd
2 from
3 xmltable('48,75,48,75,48,75'
4 columns n int path '.'
5 );
FD
----------
6
1 row selected.
SQL> select p,count(*) cnt
2 from t_params
3 group by p;
P CNT
---------- ----------
48 1
75 3
2 rows selected.
SQL> truncate table t_params;
Table truncated.
SQL> select sum((select 1 from dual where f_ssc(n)>0)) fd
2 from
3 xmltable('48,75,75,75'
4 columns n int path '.'
5 );
FD
----------
4
1 row selected.
SQL> select p,count(*) cnt
2 from t_params
3 group by p;
P CNT
---------- ----------
48 1
75 1
2 rows selected.
Turning off caching
Lets do simple test with little “_query_execution_cache_max_size” with values from 1-10000 twice:
1 to 10000, 1 to 10000
SQL> alter session set "_query_execution_cache_max_size" = 65536;
Session altered.
SQL> truncate table t_params;
Table truncated.
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('1 to 10000,1 to 10000'
4 columns n int path '.'
5 );
FD
----------
20000
1 row selected.
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
10000
1 row selected.
SQL> ----------
SQL> truncate table t_params;
Table truncated.
SQL> select sum((select 1 from dual where f_ssc(n)>0)) fd
2 from
3 xmltable('1 to 10000,1 to 10000'
4 columns n int path '.'
5 );
FD
----------
20000
1 row selected.
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
9984
1 row selected.
SQL> spool off
[collapse]
You can assume that the results in the cache are replaced by results of N last executions.
But lets do another test:
First of all let’s see how will be cached executions from 1 to 1000:
1,1-1000,1-1000,1-1000
SQL> alter session set "_query_execution_cache_max_size" = 65536;
Session altered.
SQL> truncate table t_params;
Table truncated.
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('1,1 to 1000,1 to 1000,1 to 1000'
4 columns n int path '.'
5 );
FD
----------
3001
1 row selected.
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>2;
CNT
----------
356
1 row selected.
SQL> select count(*) from t_params where p=1;
COUNT(*)
----------
1
1 row selected.
[collapse]
Now with additional 10000:
1,1-10000,1-1000,1-1000
SQL> truncate table t_params;
Table truncated.
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('1,1 to 10000,1 to 1000,1 to 1000,1 to 1000'
4 columns n int path '.'
5 );
FD
----------
13001
1 row selected.
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>2;
CNT
----------
1000
1 row selected.
SQL> select count(*) from t_params where p=1;
COUNT(*)
----------
4
1 row selected.
[collapse]
To be continued…