Skip navigation.

xtender's blog

Deterministic function vs scalar subquery caching. Part 1

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.(About hash tables and hash collisions for scalar subquery caching excelent wrote 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.

Just another version of Tom Kyte’s runstats (runstats_pkg)

I want to share my modifications of Tom Kyte's runstats package, which include:


  • Any number of runs sets for analyzing

  • Standalone: No need to create other objects

  • Ability to specify session SID for statistics gathering

  • Ability to specify what to gather: latches, stats or both

  • Separate mask filters for output by statname and latchname

  • Ability to specify difference percentage for output separately for latches and statistics

  • More accurate with some statistics because of avoiding global temporary table usage, but less in