Oracle Result Cache: Cutting Repeated Query Latency Without Rewriting Code

The Problem: Same Query, Same Data, Wasted Work

Read-heavy applications often execute identical SQL statements hundreds of times per minute against data that changes infrequently. Each execution parses, optimizes, and fetches the same rows, burning CPU and I/O for no new information. Oracle Result Cache addresses this by storing query results in the shared pool and serving subsequent identical executions from memory.

How Result Cache Works

The feature caches the output of deterministic SQL queries and PL/SQL function results in a dedicated area of the shared pool. When a statement matches a cached entry — same text, same bind values, same session environment — Oracle returns the stored rows instead of re-executing the plan. Invalidation is automatic: any DML on a referenced table marks the cache entry stale, so the next execution repopulates it. No manual flushes required.

Two modes control eligibility. RESULT_CACHE_MODE = FORCE attempts to cache every query that qualifies (deterministic, no SYSDATE, no sequences). MANUAL (the default) caches only statements explicitly hinted with /*+ RESULT_CACHE */ or functions defined with the RESULT_CACHE clause. MANUAL is safer for production because it avoids flooding the shared pool with low-value entries.

When It Pays Off

Best candidates are complex reporting queries that scan large tables, join multiple dimensions, or aggregate millions of rows — and run repeatedly against mostly static data. Think dashboard refreshes, parameterized lookup tables, or nightly batch jobs that re-read reference data. Benchmarks frequently show 50–90% latency reduction for these patterns.

OLTP workloads with frequent DML on the underlying tables see diminishing returns. Each insert, update, or delete invalidates related cache entries, adding latch contention and CPU overhead for cache maintenance. If your tables change every few seconds, the cache churn outweighs the hit benefit.

Worked Example: Verifying Cache Behavior

Assume a reporting query on a sales fact table that changes only during nightly loads:

SELECT /*+ RESULT_CACHE */
       region,
       SUM(amount) AS total_sales
FROM   sales_fact
WHERE  sale_date >= TRUNC(SYSDATE) - 30
GROUP BY region;

Run it once with RESULT_CACHE_MODE = MANUAL. Then inspect the cache:

SELECT id, type, status, name, scan_count, block_count
FROM   v$result_cache_objects
WHERE  name LIKE '%sales_fact%';

You should see a row with STATUS = 'Published' and a non-zero SCAN_COUNT. Run the query again — elapsed time drops sharply, and the plan shows a RESULT CACHE operation instead of the full scan/aggregation.

Now simulate a nightly load:

INSERT INTO sales_fact (region, amount, sale_date)
VALUES ('EMEA', 12500, SYSDATE);
COMMIT;

Re-query V$RESULT_CACHE_OBJECTS. The previous entry moves to STATUS = 'Invalid' and a new entry appears after the next execution. This automatic invalidation is the feature's safety net — you never serve stale data.

Trade-offs and Guardrails

• Shared pool pressure: Cache entries consume shared pool memory. Undersized pools lead to ORA-04031 errors. Monitor V$RESULT_CACHE_STATISTICS for Space Used vs Space Limit and size the pool accordingly (or set RESULT_CACHE_MAX_SIZE explicitly).
• Non-deterministic constructs are excluded: SYSDATE, SYSTIMESTAMP, sequences, DBMS_RANDOM, and session-specific functions (e.g., SYS_CONTEXT) prevent caching. Rewrite queries to pass dates as bind variables if you need cache eligibility.
• Bind sensitivity: Each distinct bind set creates a separate cache entry. A query with high-cardinality bind values (e.g., customer_id) can explode the cache. Use /*+ RESULT_CACHE */ selectively, not on every parameterized lookup.
• RAC considerations: In Oracle RAC, the result cache is global (shared across instances) by default. Invalidation messages traverse the interconnect. For very high DML rates, consider RESULT_CACHE_REMOTE_EXPIRATION or instance-local caching via RESULT_CACHE_MODE = FORCE with careful testing.

Actionable Next Steps

1. Identify your top 5 most-executed, longest-running read-only queries from V$SQL (filter by EXECUTIONS > 100 and ELAPSED_TIME_PER_EXEC > 1s).
2. Confirm they are deterministic: no SYSDATE, no sequences, no session-dependent functions.
3. Add /*+ RESULT_CACHE */ to one candidate in a non-production environment. Set RESULT_CACHE_MODE = MANUAL at session level first.
4. Run the query twice, capture elapsed time from V$SQL_MONITOR or SQL*Plus timing, and verify a RESULT CACHE operation appears in the plan (DBMS_XPLAN.DISPLAY_CURSOR).
5. Check V$RESULT_CACHE_OBJECTS for the entry and its hit count. Perform a DML on a referenced table and confirm invalidation.
6. If results are positive, size the shared pool for the expected cache footprint (start with RESULT_CACHE_MAX_SIZE = 100M and adjust based on V$RESULT_CACHE_STATISTICS).

Result Cache is a targeted tool, not a universal accelerator. Apply it to the right queries — stable data, complex reads, high repetition — and you'll reclaim CPU cycles without touching application code. Monitor the shared pool, respect the invalidation cost, and you'll keep the wins without the ORA-04031 surprises.