Managing the Server Result Cache
How the Server Result Cache Works
Server Result Cache Initialization Parameters
- This parameter sets the memory allocated to the server result cache. The server result cache is enabled unless you set this parameter to 0, in which case the cache is disabled.
- This parameter sets the maximum amount of server result cache memory that can be used for for a single result. The default is 5%, but you can specify any percentage value between 1 and 100. You can set this parameter at the system or session level.
- This parameter specifies the expiration time for a result in the server result cache that depends on remote database objects. The default value is 0 minutes, which implies that results using remote objects should not be cached.
Managing Server Result Cache Memory with Initialization Parameters
- When using the MEMORY_TARGET initialization parameter to specify the memory allocation, Oracle Database allocates 0.25% of MEMORY_TARGET to the result cache.
- When you set the size of the shared pool using the SGA_TARGET initialization parameter, Oracle Database allocates 0.50% of SGA_TARGET to the result cache.
- If you specify the size of the shared pool using the SHARED_POOL_SIZE initialization parameter, then Oracle Database allocates 1% of the shared pool size to the result cache.
Client Result Cache Initialization Parameters
Sets the maximum size of the client result cache for each client process. To enable the client result cache, set the size to 32768 bytes or greater. A lesser value, including the default of 0, disables the client result cache.
Note: If the CLIENT_RESULT_CACHE_SIZE setting disables the client cache, then a client node cannot enable it. If the CLIENT_RESULT_CACHE_SIZE setting enables the client cache, however, then a client node can override the setting. For example, a client node can disable client result caching or increase the size of its cache.
Specifies the amount of lag time for the client result cache. If the OCI application performs no database calls for a period of time, then the client cache lag setting forces the next statement execution call to check for validations.
If the OCI application accesses the database infrequently, then setting this parameter to a low value results in more round trips from the OCI client to the database to keep the client result cache synchronized with the database. The client cache lag is specified in milliseconds, with a default value of 3000 (3 seconds).
Specifies the release with which Oracle must maintain compatibility. For the client result cache to be enabled, this parameter must be set to 18.104.22.168 or higher. For client caching on views, this parameter must be set to 22.214.171.124.0 or higher.
Result Cache Mode
Query results can only be stored in the result cache by using a query hint or table annotation. This is the recommended value.
All 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 statement, including the result cache hint, retrieve data from the cache.
Sessions uses these results if possible. To exclude query results from the cache, you must use the /*+ NO_RESULT_CACHE */ query hint.
Note: FORCE mode is not recommended because the database and clients attempt to cache all queries, which can create significant performance and latching overhead.
Lists various server result cache settings and memory usage statistics.
Lists all the memory blocks in the server result cache and their corresponding statistics.
Lists all the objects whose results are in the server result cache along with their attributes.
Lists the dependency details between the results in the server cache and dependencies among these results.
Stores cache settings and memory usage statistics for the client result caches obtained from the OCI client processes. This statistics table has entries for each client process that is using result caching. After the client processes terminate, the database removes their entries from this table. The client table lists information similar to V$RESULT_CACHE_STATISTICS.
DBA_TABLES, USER_TABLES, ALL_TABLES
Includes a RESULT_CACHE column that shows the result cache mode annotation for the table. If the table has not been annotated, then this column shows DEFAULT. This column applies to both server and client result caching.