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 designated 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 run time will be standard but for the subsequent queries, it will be very fast.

This cache is part of the shared pool.

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 dependent objects are modified (parameter set to 0)

By default, the size of the result cache depends of you initialization parameters.

1. If MEMORY_TARGET parameter is set, then the result cache is 0.25% of this value.
2. If SGA_TARGET is set, then the result cache value is 0.5% of this value.
3. If you manage the shared pool manually 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.

SELECT /*+ RESULT_CACHE */ SUM(emp.emp_id), dep.dep_name

  FROM employees emp,

       departments dep

 where emp.dep_id = dep.dep_id

 GROUP BY dep.dep_name

 ORDER BY dep.dep_name;

Using the result cache in functions

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

CREATE OR REPLACE FUNCTION format_customer_name (

                             p_cust_id IN customers.cust_id%TYPE

                             ) RETURN VARCHAR2

                               RESULT_CACHE

                               RELIES_ON (customers) IS

     v_name VARCHAR2(4000);

BEGIN

   counter.increment();

   SELECT cust_first_name || ' ' || cust_last_name    INTO   v_name

   FROM   customers    WHERE  cust_id = p_cust_id;

   RETURN v_name;

END format_customer_name;

/