2.346 RESULT_CACHE_MODE

RESULT_CACHE_MODE specifies which queries are eligible to store result sets in the result cache. Only query execution plans with the result cache operator will attempt to read from or write to the result cache.

Property Description

Parameter type

String

Syntax

RESULT_CACHE_MODE = { MANUAL | MANUAL_TEMP | FORCE | FORCE_TEMP }

Default value

Oracle Autonomous Database: FORCE

All other Oracle databases: MANUAL

Modifiable

ALTER SESSION, ALTER SYSTEM

Modifiable in a PDB

Yes

Basic

No

Oracle RAC

Different instances can have different values.

Values

  • MANUAL

    Query results can only be stored in the result cache by using a query hint or table annotation.

  • MANUAL_TEMP

    Query results can only be stored in the result cache by using a query hint or table annotation. All hinted queries are allowed to leverage temporary segments on disk unless explicitly prohibited by using the /*+ RESULT_CACHE (TEMP=FALSE) */ hint.

  • FORCE

    All query results are stored in the result cache. If a query result is not in the cache, then the database executes the query and stores the result in the cache. Subsequent executions of the same SQL statement that include the /*+ RESULT_CACHE */ hint will retrieve data from the cache. Sessions will use these results, if possible. To exclude query results from the cache, use the /*+ NO_RESULT_CACHE */ hint.

  • FORCE_TEMP

    All query results are stored in the result cache. All queries are allowed to leverage temporary segments on disk unless explicitly prohibited by a hint.

Caution:

If this parameter is set to FORCE or FORCE_TEMP, then Oracle recommends setting the RESULT_CACHE_INTEGRITY parameter to ENFORCED, which ensures that only deterministic constructs are eligible for result caching. This prevents the caching of nondeterministic constructs, which can potentially cause material changes to results. See "RESULT_CACHE_INTEGRITY" for more information.

See Also: