3.2. Procedure Result Caching

Cached virtual procedure results are used automatically when a matching set of parameter values is detected for the same procedure execution. Usage of the cached results may be bypassed when used with the OPTION NOCACHE clause.

To indicate that a virtual procedure is to be cached, its definition must include a Cache Hint.

/*+ cache */
BEGIN
    ...
END

Results will be cached with the default ttl.

The pref_mem and ttl options of the cache hint may also be used for procedure caching.

Procedure results cache keys include the input parameter values. To prevent one procedure from filling the cache, at most 256 cache keys may be created per procedure per VDB.

A cached procedure will always produce all of its results prior to allowing those results to be consumed and placed in the cache. This differs from normal procedure execution which in some situations allows the returned results to be consumed in a streaming manner.