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 AI 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:

• Oracle AI Database SQL Tuning Guide for more information on how the result cache handles PL/SQL functions before changing the value of this initialization parameter

• Oracle AI Database SQL Language Reference for more information about the NO_RESULT_CACHE hint