In previous parts 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.
- Oracle doesn’t keep last result of deterministic functions as it does for scalar subquery caching
- 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.
Upd 2015-02-19:
A couple additions about deterministic functions:
Today’s post is just addition to previous topics:
I wrote about turning caching off after many unsuccessfull attempts to get value from cache, but i didn’t say what it is the number. In fact caching of deterministic functions also depends on two another hidden parameters:
SQL> @param_ plsql%cach NAME VALUE DEFLT TYPE DESCRIPTION ------------------------------------ ------------ -------- ---------- ------------------------------------------------------------------ _plsql_cache_enable TRUE TRUE boolean PL/SQL Function Cache Enabled _plsql_minimum_cache_hit_percent 20 TRUE number plsql minimum cache hit percentage required to keep caching active
First parameter “_plsql_cache_enable” is just a parameter which enables/disables this caching mechanism.
But the second parameter – “_plsql_minimum_cache_hit_percent” – is responsible for the percentage of unsuccessful attempts which disables caching.
I will show their effects with the example from the previous post:
-- set this parameter to big value for maximizing caching:
alter session set "_query_execution_cache_max_size" = 131072;
-- log table clearing:
truncate table t_params;
-- test with percentage = 50
alter session set "_plsql_minimum_cache_hit_percent"=50;
select sum(f_deterministic(n)) fd
from
xmltable('1 to 10000,1 to 10000'
columns n int path '.'
);
select 10000-count(count(*)) "Count of cached results"
from t_params
group by p
having count(*)>1;
/*
Count of cached results
-----------------------
0
*/
-- now i change cache hit percentage parameter to 0:
alter session set "_plsql_minimum_cache_hit_percent"=0;
truncate table t_params;
select sum(f_deterministic(n)) fd
from
xmltable('1 to 10000,1 to 10000'
columns n int path '.'
);
select 10000-count(count(*)) "Count of cached results"
from t_params
group by p
having count(*)>1;
/*
Count of cached results
-----------------------
2039
*/