Deep Dive — RESULT_CACHE

A function whose answers are stored in the SGA, shared by every session, and thrown away automatically the moment the underlying table changes.

Used in
Chapter 3 — Functions Inside SQL
Appendix entry
RESULT_CACHE
Related
DETERMINISTIC ASSOCIATIVE ARRAY PRAGMA UDF

A RESULT_CACHE function stores each (arguments → result) pair in the server result cache (part of the shared pool). A second call with the same arguments — from any session — returns the stored result without running the body. Oracle tracks which tables the function read and invalidates the cached results when any of them changes.

The sweet spot: small, rarely changing lookup tables (configuration, code tables, tiers, rates) read by many sessions. Not for: big tables, volatile tables, high-cardinality arguments.

The SQL-level sibling is the /*+ RESULT_CACHE */ hint on a query.

SQL 
CREATE [OR REPLACE] FUNCTION <name> (<params>) RETURN <type>
  RESULT_CACHE [RELIES_ON (<table>, ...)]     -- RELIES_ON is ignored since 11.2 (auto-tracked)
IS ... END;

-- management
DBMS_RESULT_CACHE.STATUS          -- ENABLED / DISABLED / BYPASS
DBMS_RESULT_CACHE.MEMORY_REPORT   -- usage
DBMS_RESULT_CACHE.FLUSH           -- drop everything
DBMS_RESULT_CACHE.INVALIDATE(owner, name)
DBMS_RESULT_CACHE.BYPASS(TRUE)    -- temporarily disable (e.g. during a bulk load)

-- parameters (DBA): RESULT_CACHE_MAX_SIZE, RESULT_CACHE_MAX_RESULT (% per result), RESULT_CACHE_MODE
-- views: V$RESULT_CACHE_OBJECTS, V$RESULT_CACHE_STATISTICS, V$RESULT_CACHE_DEPENDENCY

Restrictions: no OUT/IN OUT parameters; arguments and result must be scalar, record, or collection of scalars (no LOB, ref cursor, object with LOB); no invoker's rights; no side effects (the body may not run when cached); pipelined functions excluded.

SQL 
CREATE OR REPLACE FUNCTION tier_discount (p_tier VARCHAR2) RETURN NUMBER
  RESULT_CACHE
IS
  v_pct NUMBER;
BEGIN
  SELECT discount_pct INTO v_pct FROM tier_rates WHERE tier = p_tier;   -- 4-row table
  RETURN v_pct;
END;
/
SELECT order_id, amount * (1 - tier_discount(c.tier) / 100) AS net
FROM   orders o JOIN customers c ON c.customer_id = o.customer_id
WHERE  o.order_date >= DATE '2026-01-01';

4 distinct arguments → 4 cache entries → the SELECT inside the function runs 4 times for 40M rows, across every session. (The function call still happens 40M times; only the body is skipped — see PRAGMA UDF.)

SQL 
SELECT name, value FROM v$result_cache_statistics
WHERE  name IN ('Create Count Success', 'Find Count', 'Invalidation Count');

SELECT id, type, status, name, row_count, scan_count
FROM   v$result_cache_objects
WHERE  name LIKE '%TIER_DISCOUNT%';
-- status: Published = usable, Invalid = a dependency changed, Bypass = disabled

Find Count climbing while Create Count stays flat = hits.

SQL 
UPDATE tier_rates SET discount_pct = 12 WHERE tier = 'GOLD';
COMMIT;
-- every cached result of tier_discount is now Invalid, for every argument, in every session.
-- The next call per argument recomputes and republishes.

Any committed DML on any table the function read invalidates all its results. That is correct, and it is why volatile tables make the cache useless (constant churn).

SQL 
CREATE OR REPLACE FUNCTION customer_tier (p_id NUMBER) RETURN VARCHAR2
  RESULT_CACHE
IS ... SELECT tier INTO v FROM customers WHERE customer_id = p_id ... END;

5M distinct arguments → up to 5M cache entries competing for RESULT_CACHE_MAX_SIZE (typically a few hundred MB, shared with the SQL result cache); every update to customers invalidates all of them; and every miss takes the result-cache latch. Under load this is slower than the plain query. For this shape, use a join, or a session-level ASSOCIATIVE ARRAY.

SQL 
BEGIN
  DBMS_RESULT_CACHE.BYPASS(TRUE);       -- this session: no cache reads or writes
  load_tier_rates;                      -- many updates → would invalidate constantly
  DBMS_RESULT_CACHE.BYPASS(FALSE);
END;

  • Latch contention. The result cache is protected by a single latch (Result Cache: RC Latch). Many sessions missing at the same time (high-cardinality arguments, or right after an invalidation) serialize on it. Symptom: latch free waits on that latch in AWR.
  • Memory. RESULT_CACHE_MAX_SIZE is shared by all result-cache users. A function with millions of distinct arguments evicts everyone else's entries.
  • RAC. Each instance has its own cache; invalidations are propagated, but hits are per instance.
  • Invalidation storms. A dependency table updated every few seconds → results are invalid most of the time → all cost, no benefit. Check Invalidation Count vs Find Count.
  • Per-call overhead remains. The context switch SQL→PL/SQL still happens per row; the cache only removes the body's work. Combine with PRAGMA UDF, or inline the lookup as a join when the table is small anyway.

  1. Caching a function of SYSDATE, SYS_CONTEXT, USER — the cache ignores those inputs; every session gets the first session's answer.
  2. Side effects in the body (logging, counters) — they stop happening once the result is cached.
  3. Expecting RELIES_ON to matter — ignored since 11.2; dependencies are tracked automatically, including through views.
  4. RESULT_CACHE_MAX_SIZE = 0 — the cache is disabled at instance level; the function silently runs uncached (DBMS_RESULT_CACHE.STATUS says DISABLED).
  5. Result larger than RESULT_CACHE_MAX_RESULT (default 5% of the cache) — not cached, silently.
  6. Functions on tables with VPD policies or on remote tables (database links) — not cached.
  7. Using it as "free memoization" for a per-row lookup on a big table — see 3d.