Result Cache concept and benefits

This feature was first introduced in Oracle 11g and was meant to increase performance of repetitive queries returning the same data.
The Result Cache feature is interesting if your application always look for static data, or data that is rarely updated, for these reasons, it is firstly destinated to Data Warehouses databases (OLAP) as many users will look for the same data via reporting tools.
This is particularly useful when you query a large amount of data to return only a few rows, for example, the last month sales aggregated by week.
Oracle will have to look for a large amount of data to retrieve all sales and make the aggregate, but the result will be only 4 rows.
For the first query execution, the runtime will be standard but for the subsequent queries, it will be very fast.
This cache is part of the shared pool.

Result Cache

Managing the Result Cache

The Result Cache is managed by 3 initialization parameters:

PARAMETER DESCRIPTION
RESULT_CACHE_MAX_SIZE Used to managed the size of the result cache, if set to 0, the result cache is disabled
RESULT_CACHE_MAX_RESULT Used to define the max percentage of the result cache a single query can use.
RESULT_CACHE_REMOTE_EXPIRATION This parameter defines the time in minutes that the result cache remains valid. By default the cache is valid until the dependant objects are modified (parameter set to 0)

By default, the size of the result cache depends of you initialization parameters.
If MEMORY_TARGET parameter is set, then the result cache is 0.25% of this value.
If SGA_TARGET is set, then the result cache value is 0.5% of this value.
If you manage the shared pool manualy by setting the SHARED_POOL_SIZE parameter, then the result cache size is 1% of this value assigned to the shared pool.

How to use the result cache ?

The result cache can be used in two ways.

Using the result cache in queries

To use the result cache in queries, you need to add the RESULT_CACHE hint in the query.

Using the result cache in functions

To use it in functions, you must mention it in the create statement.

Performance improvement

here is a small test to show the benefits of using the result cache.

The first execution is a normal execution and Oracle caches the rows into the shared pool, as you can see just under the plan, oracle gets statistics about the data to cache; the number of columns and the dependent objects.
Remember that if you alter the dependent object, the data cached is invalidated.
To see what is actually cached into the result cache, we can query this view

The cache_id used in the query is from the column name of the plan. As you can see the rows from my query are cached.
Now let’s see what happens if I execute the same query a second time.

When we look at the plan statistics, there is a huge improvement, nothing has been done, the data is just retrieved from the cache without any overhead.
Now we will alter the dependant object to see how Oracle handles it.

I issued a delete statement on the table, immediately oracle invalidate the data in the result cache and will no longer use it until the transaction is committed or rolled back, even if I issue the same statement again.
Now I issue a commit and run the statement again.

For the first execution, oracle cache the altered data. Now we issue the statement again.

Oracle cached the new data in the result cache.

I have now two sets in the result cache, one before and one after the delete.

As you can see, you can expect to huge performance benefits by using this feature, but to be efficient, you have to select the queries you choose to cache with caution because it can rapidly be memory consuming and can result in latch contention.

Leave a Reply