Cross Session Caching With Zero Application Code
The PL/SQL function result cache, introduced in Oracle 11g, allows the database to automatically cache the return values of a function based on its input parameters. Unlike application level caching, this cache is maintained in the SGA (shared memory) and is available to all sessions. When any session calls the function with previously cached input values, Oracle returns the cached result without executing the function body at all.
CREATE OR REPLACE FUNCTION get_tax_rate(
p_state_code IN VARCHAR2,
p_category IN VARCHAR2
)
RETURN NUMBER
RESULT_CACHE
RELIES_ON (tax_rates)
IS
l_rate NUMBER;
BEGIN
SELECT rate
INTO l_rate
FROM tax_rates
WHERE state_code = p_state_code
AND category = p_category;
RETURN l_rate;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END get_tax_rate;
/The RELIES_ON Clause
The RELIES_ON clause tells Oracle which tables the function depends on. When any session performs DML on those tables and commits, Oracle automatically invalidates the relevant cached results. This ensures the cache never serves stale data. In Oracle 12c and later, Oracle can automatically detect table dependencies even without the RELIES_ON clause, but explicitly listing dependencies is still considered good practice for documentation and for ensuring correct behavior in edge cases.
Ideal Use Cases
The result cache works best for functions that read from reference data tables that change infrequently (tax rates, configuration parameters, lookup codes), are called frequently with a limited set of distinct input combinations, and are relatively expensive to execute compared to a cache lookup. Functions that translate status codes, retrieve configuration values, or perform complex calculations on slowly changing data are perfect candidates.
The Gotchas You Must Understand
The result cache has several important limitations and behaviors that can surprise you if you are not prepared:
Invalidation granularity: When any row in a dependent table changes, Oracle invalidates the entire cache for that function, not just the entries that correspond to the changed row. If the dependent table receives frequent updates (even to unrelated rows), the cache hit rate will be poor and the function may actually perform worse with caching enabled due to the overhead of cache management.
Session specific state: The result cache does not capture session specific state. If your function uses SYS_CONTEXT, session variables, or NLS settings that vary between sessions, the cached result from one session could be incorrect for another. Functions that rely on session state should not use RESULT_CACHE.
Memory consumption: Cached results consume SGA memory. If your function has a very high number of distinct input combinations (millions of possible parameter value sets), the cache may consume excessive memory or suffer from frequent evictions. Monitor cache usage with V$RESULT_CACHE_STATISTICS and V$RESULT_CACHE_OBJECTS.
-- Monitor result cache effectiveness
SELECT name, value
FROM V$RESULT_CACHE_STATISTICS;
-- See what is currently cached
SELECT name, type, status, scan_count, row_count
FROM V$RESULT_CACHE_OBJECTS
WHERE type = 'Result'
ORDER BY scan_count DESC;Result Cache in Oracle 19c Through 23ai
Oracle 19c improved result cache performance in Real Application Clusters (RAC) environments, where cache invalidation across instances was historically expensive. Oracle 21c introduced enhancements to the automatic dependency detection mechanism. In 23ai, the result cache has been further optimized for multitenancy environments (PDBs), with better isolation between pluggable databases sharing the same CDB.
The configuration parameters RESULT_CACHE_MAX_SIZE and RESULT_CACHE_MAX_RESULT control the total cache size and the maximum proportion that a single function can consume, respectively. Tuning these parameters is important in production environments to prevent one heavily cached function from starving others.