Deterministic function vs scalar subquery caching. Part 2

Posted on by Posted in deterministic functions, oracle, query optimizing, scalar subquery caching 2,839 Page views 3 Comments

In previous part i already point out that:

  1. Both mechanisms are based on hash functions.
  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.

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

[sourcecode language=”sql” highlight=”28,56″]
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

[/sourcecode]

[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:
1,1-1000,1-1000,1-1000

[sourcecode language=”sql” highlight=”28,36″]
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.
[/sourcecode]

[collapse]
Ok, only 356 from 1000 results was not cached.
Now with additional 10000:
1,1-10000,1-1000,1-1000

[sourcecode language=”sql” highlight=”24,32″]
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.
[/sourcecode]

[collapse]
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…

Page views: 2,839