How to Manage the Result Cache with DBMS_RESULT_CACHE

The server result cache keeps the results of queries and functions in memory, so repeated calls return them without running again. DBMS_RESULT_CACHE manages it: report its status and memory, flush it, and invalidate the results that depend on an object.

Code for This Guide

The main examples are in the examples/pkg-performance folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_result_cache.status                       -- function: ENABLED, DISABLED, ...
dbms_result_cache.memory_report;
dbms_result_cache.flush;
dbms_result_cache.invalidate(owner, name);

Managing the cache needs EXECUTE on DBMS_RESULT_CACHE, a DBA privilege. V$RESULT_CACHE_OBJECTS lists the cached results.

Cache, Reuse, and Invalidate a Result

Run as a DBA. The cache is flushed, and the same query with the RESULT_CACHE hint runs twice.

Example:

exec dbms_result_cache.flush
select dbms_result_cache.status as cache_status from dual;

select /*+ result_cache */ count(*) as dxb_routes from nimbus.routes where origin = 'DXB';
select /*+ result_cache */ count(*) as dxb_routes from nimbus.routes where origin = 'DXB';

select status, scan_count as reused from v$result_cache_objects
where  type = 'Result' and name like '%dxb_routes from nimbus.routes%';

-- invalidate the results that depend on ROUTES, as a change to it would
exec dbms_result_cache.invalidate('NIMBUS', 'ROUTES')

select status, scan_count as reused from v$result_cache_objects
where  type = 'Result' and name like '%dxb_routes from nimbus.routes%';

Output:

PL/SQL procedure successfully completed.

CACHE_STATUS
_______________
ENABLED

   DXB_ROUTES
_____________
           20

   DXB_ROUTES
_____________
           20

STATUS          REUSED
____________ _________
Published            1

PL/SQL procedure successfully completed.

STATUS        REUSED
__________ _________
Invalid            1

The first run publishes the result, and the second reuses it: SCAN_COUNT is 1. INVALIDATE for NIMBUS.ROUTES marks the result Invalid, exactly as a committed change to ROUTES would, so the next run computes it again.

Things to Know

• Results are invalidated automatically when tables they depend on change, so the cache never returns stale data.
• Cache results of queries and functions that are called often with the same inputs over rarely changing tables.
• MEMORY_REPORT prints how much memory the cache uses, through DBMS_OUTPUT.

Related Guides

• How to Cache Function Results with RESULT_CACHE in PL/SQL
• How to Trace Sessions with DBMS_MONITOR

Conclusion

DBMS_RESULT_CACHE reports on, flushes, and invalidates the server result cache. Let Oracle invalidate results on changes, and use the package to inspect the cache or clear it when testing.