In previous part i already point out that:

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]

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]

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]

As you see, all 10000 repeated calls deterministic function was executed! There is no any cached result unlike as with SSC.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:Ok, only 356 from 1000 results was not cached.Now with additional 10000:We see now, that even last calls from 1 to 1000 was not cached, but second call with n=1 was cached.

To be continued…