Tuning the Result Cache

This post explores how to optimize database performance by tuning the result cache.

About the Result Cache

The result cache stores query results in memory, either in the Server Global Area (SGA) or the client application memory. This allows for faster retrieval of previously executed queries, reducing query execution time.

Server Result Cache

Concepts

• Location: Within the shared pool in the SGA.
• Components: SQL query result cache and PL/SQL function result cache.

Benefits

• Improved OLAP Performance: Especially beneficial for queries that access many rows but return few, such as those familiar in data warehouses.
• Reduced Query Execution Time: Retrieves results from memory instead of re-executing the query.
• Optimized Resource Utilization: Frees up server resources for other tasks.

How It Works

1. Query Execution: When a query is executed, the database checks the cache for pre-existing results.
2. Result Retrieval: If the result is found, it’s retrieved from memory.
3. Result Caching: If the result is not found, the database executes the query, stores the result in the cache, and returns it.

Examples

• RESULT_CACHE Hint:

SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) FROM hr.employees GROUP BY department_id

• This hint forces the query to use the result cache.
• V$RESULT_CACHE_OBJECTS View: Provides detailed statistics about cached results.

Client Result Cache

Concepts

• Location: Client process memory, shared across sessions within the process.
• Benefits:
• Reduced query response time.
• More efficient use of database resources.
• Lower memory cost.
• Limitations:
• It only caches the results of the outermost query, not the subqueries.

How It Works

• Client-Side Caching: Results are stored locally on the client.
• Consistency: The cache is kept synchronized with changes in the database, ensuring data accuracy.

Configuring the Result Cache

Server Result Cache

Server Result Cache Initialization Parameters

DBMS_RESULT_CACHE Package

• MEMORY_REPORT Procedure: Displays statistics about result cache memory usage.
• FLUSH Procedure: Clears all cached results and frees up memory.
• Example: Viewing Memory Usage Statistics:

SET SERVEROUTPUT ON
BEGIN  
   DBMS_RESULT_CACHE.MEMORY_REPORT;
END;
/


Result Cache Memory Report
[Parameters]
Block Size = 1024 bytes
Maximum Cache Size = 950272 bytes (928 blocks)
Maximum Result Size = 47104 bytes (46 blocks)
[Memory]
Total Memory = 46340 bytes [0.048% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.011% of the Shared Pool]
... State Object Pool = 2852 bytes [0.003% of the Shared Pool]
... Cache Memory = 32792 bytes (32 blocks) [0.034% of the Shared Pool]
....... Unused Memory = 30 blocks
....... Used Memory = 2 blocks
........... Dependencies = 1 blocks
........... Results = 1 blocks
............... SQL = 1 blocks

PL/SQL procedure successfully completed.

Client Result Cache

Client Result Cache Initialization Parameters

An optional client configuration file overrides client result cache initialization parameters set in the server parameter file.

Note: The client result cache lag can only be set with the CLIENT_RESULT_CACHE_LAG initialization parameter.

Setting the Result Cache Mode

RESULT_CACHE_MODE Parameter

Requirements for the Result Cache

• Read Consistency: The cached result must be read-consistent, meaning it reflects a consistent state of the data.
• Query Parameters: Cache results can be reused if they are parameterized with the same bind variable values.
• Restrictions: Certain database objects and functions are not eligible for caching.

Exam Tip:

Result cache does not work on an Active Data Guard standby database opened in read-only mode.

Specifying Queries for Result Caching

SQL Result Cache Hints

• RESULT_CACHE: Enables caching for a specific query.
• NO_RESULT_CACHE: Prevents caching of a query.

Result Cache Table Annotations

• RESULT_CACHE: Specifies caching behavior for specific tables.

Exam Tips:

If a table has its result_cache attribute configured to the default mode and the result_cache hint is included in a single-table query for that table, the query’s results can be cached.

If a table has its result_cache attribute set to mode default and the session is configured with result_cache_mode=force, then the results of a single-table query on that table, which does not include any hints, can be cached.

If at least one table in a query is set to DEFAULT, then result caching is not enabled at the table level for this query, unless if the RESULT_CACHE_MODE initialization parameter is set to FORCE or the RESULT_CACHE hint is specified. This is the default value.

If all the tables of a query are marked as FORCE, then the query result is considered for caching. The table annotation FORCE takes precedence over the RESULT_CACHE_MODE parameter value of MANUAL set at the session level.

Monitoring the Result Cache

• V$RESULT_CACHE_OBJECTS View: Provides detailed statistics about cached results.
• DBMS_RESULT_CACHE Package: Allows for managing and monitoring the cache.

Summary

Tuning the result cache can improve database performance, particularly for read-only or read-mostly workloads. By understanding the different types of result cache, their configuration options, and the requirements for caching, you can optimize your database for optimal performance.

For more info, see:
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-result-cache.html#GUID-57DDBE23-9A6D-4556-AEF5-01D427BA716D