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.

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