What is Result Cache and How do I use it?

 

Result Cache is the memory space in the shared pool area within the Shared Global Area (SGA). In this memory area, the database query result or the query block is stored for reuse. This memory space is shared by cached blocks, sql statements, and non-stale sessions.

The use of the Result Cache is ideal for tables that are small in size and require frequent access. Using for large tables may cause a problem. However, it will provide very serious gains in small tables which are constantly accessed.

The Result Cache came with the Oracle Database 11g Release 1 version and is enabled by default.

We can learn the Result Cache status with the following query.

It is useful to set two important parameters related to the result cache. To avoid a bottleneck, you must assign a value as follows to result_cache_max_size and shared_pool_size. You can set these parameters according to your memory or according to your needs.

You will then need to restart the database.

To use the Result Cache, you need to add  /*+ RESULT_CACHE */ hint to the query. The sample query is as follows.

The use of result cahce is manually managed by default. If we wish, we can also force it to be used automatically. This is possible according to the value of the result_cache_mode parameter.

The value of this parameter is MANUAL by default. We can force the system to use result cache by setting this parameter as FORCE. The usage is limited by the result_cache_max_size parameter.

We can change the result_cache_mode parameter as follows:

Even if the result_cache_mode parameter is set to FORCE, the result cache will not be used if NO_RESULT_CACHE query hint is specified in the query. Query Hint is always superior from the parameter.

Querying cached objects :

With the following query, you can find the objects used result cache.

The result cache may need to be flushed in some cases. You can clear existing cache as follows.

Memory status must also be monitored when using the Result Cache. You can mointor it with the Result Cache Memory Report. You can create this report as follows. With this report, we can see the memory usage and configurations.

“v$result_cache_statistics” is another view where you can get information about the Result Cache.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *