Tag Archives: scalar subquery caching

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.
Continue reading→

Deterministic function vs scalar subquery caching. Part 1

Posted on by Posted in deterministic functions, oracle, scalar subquery caching 8,635 Page views 8 Comments

I recently did a comparison caching mechanisms of scalar subquery caching(SSC) and deterministic functions in 11.2. Unfortunately, I do not have enough time to do a full analysis, so I will post it in parts.

Today’s topics:
1. Both mechanisms are based on hash functions.(You can read great articles about hash tables and hash collisions for scalar subquery caching by Tom Kyte and Jonathan Lewis(“Cost-Based Oracle fundamentals” chapter 9))
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.

UPD: Part 2
Continue reading→