Oracle SQL
  • LICENSE

Deterministic function vs scalar subquery caching. Part 2

Posted on February 11, 2013 by Sayan Malakshinov Posted in deterministic functions, oracle, query optimizing, scalar subquery caching 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

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]
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

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]
Ok, only 356 from 1000 results was not cached.
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]
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…

deterministic functions oracle scalar subquery caching
« Deterministic function vs scalar subquery caching. Part 1
About unnecessary work with predicate “field=:bind_variable” where bind_variable is null »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL