Oracle 11g Result Cache

Result Caching is a new feature introduced in Oracle 11g. Since its induction in the 11g release, caching of result sets has emerged out as one of the most efficient performance tip in modern database applications. It can be implemented in both SQL and PL/SQL. In this tutorial, we shall see the result cache feature implementation and study its impacts.

 Result Cache: Background

Prior to 11g release, Oracle used to cache the database blocks, and not the result sets, in buffer cache component of Oracle memory. Since this storage was distributed, it was efficient only for first few subsequent executions of the query. Oracle 11g edition has upgraded this feature by caching the complete result sets in a new memory component, known as Result Cache.

Result Cache: How it works?

On a result cache enabled database server, if an SQL query or PL/SQL function is executed in cache mode, Oracle caches the data result set in the cache component of the memory. This component has been newly introduced in Oracle 11g memory architecture as shown in the below figure.

Now, this cached result would be shared by the server for all the subsequent executions of the query or function call, occurring across the sessions. The result sharing process evolves out as much efficient and performance oriented than the normal repetitive execution process.

The cached result also maintains the latest status of underlying participating tables (SQL) and input parameters (PL/SQL). If the table data is updated, table definition is altered, or function definition is altered, the corresponding cache gets invalidated.

Result Cache Parameters

Oracle uses four initialization parameters to enable result caching feature. This parameter setting is a necessary exercise to allow server to cache the results for the SQL statements and PL/SQL functions executed on it.

• RESULT_CACHE_MODE – The parameter describes the cache mode to be followed by the server. Currently, there are two behavioral modes available, FORCE and MANUAL.
  .
  In FORCE mode, server caches the query results at high priority. The RESULT_CACHE hint is insignificant in this mode. But, if result is not required to be cached, NO_RESULT_CACHE hint is required.
  .
  In MANUAL mode, developer has to specify the cache hint RESULT_CACHE explicitly.
  .
• RESULT_CACHE_MAX_SIZE – The parameter defines the maximum portion in SGA, which is allocated for cache memory. If the parameter value is zero, caching feature is disabled. It must be a positive integral value. Following the recommendation from Oracle, the parameter value is advised to be derived from any of the below calculations.
  • 1. 0.25% of MEMORY_TARGET parameter value
  • 2. 0.5% of SGA_TARGET parameter value
  • 3. 1% of SHARED_POOL_SIZE parameter value
• RESULT_CACHE_MAX_RESULT – The parameter allocates the maximum size of a single result set to be cached. It is expressed as a percentage value of RESULT_CACHE_MAX_SIZE. It must be a positive integral value. By default, its value is 5%.
  .
• RESULT_CACHE_REMOTE_EXPIRATION – The parameter takes the cached result retention time in minutes. By default, its value is zero.

These parameters can be set at server level by using ALTER SYSTEM command.

ALTER SYSTEM SET RESULT_CACHE_MODE = MAUAL
ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE = 300M
ALTER SYSTEM SET RESULT_CACHE_MAX_RESULT = 10
ALTER SYSTEM SET RESULT_CACHE_REMOTE_EXPIRATION = 100

At session level, the parameters can be set using ALTER SESSION command.

Their value can be viewed by querying the initialization parameters in V$PARAMETERS or simply by using SHOW PARAMETER in command line SQL

SHOW PARAMETER RESULT_CACHE ; 
 
NAME                               TYPE     VALUE 
------------------------------- ---------- ------- 
result_cache_max_result          INTEGER     10 
result_cache_max_size            big INTEGER 300M 
result_cache_mode                string      MANUAL 
result_cache_remote_expiration   INTEGER     100

Caching Performance Views

The dictionary views listed below contain information about the server cache configuration and result caching related information.

Result Cache in SQL

For caching a result set in an SQL query, Oracle 11g introduces a new optimizer hint RESULT_CACHE. I shall illustrate the hint usage with the below example.

Illustration 1:

The SQL query below selects the salary of the employee 160. Note the usage of RESULT_CACHE hint in the SELECT statement. This is first execution of the query.

SQL> SET TIMING ON
 
SQL> SELECT /*+RESULT_CACHE*/SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 160
    SALARY
----------
      4200
 
Elapsed: 00:00:00.60

The explain plan of the above query shows the usage of the RESULT CACHE operator and generates the CACHE_ID as ‘94waajh6x9swf27stbrhmf46mt’.

The above cache id can be queried in V$RESULT_CACHE_OBJECTS dictionary view. The dictionary view captures and maintains the details of cached result, SQL query, and its status.

SELECT ID, TYPE, CREATION_TIMESTAMP, STATUS, NAME
FROM V$RESULT_CACHE_OBJECTS
WHERE CACHE_ID = ’94waajh6x9swf27stbrhmf46mt’

        ID TYPE     CREATION_  STATUS    NAME
——– ———- ——— ——— ———————————————————-
         1  Result     07-APR-11     Published     SELECT /*+RESULT_CACHE*/SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID =160

Note the TYPE, STATUS and NAME of the cached result set. For SQL statement result cache, TYPE is ‘Result’. For successful and valid cached result, STATUS is ‘Published’. NAME column captures the complete SQL statement associated with the cached result.

Now, we re-execute the above query and compare the execution time.

SQL> SELECT /*+RESULT_CACHE*/SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 160
 
    SALARY
----------
      4200
 
 Elapsed: 00:00:00.10

The query execution time has considerably decreased by ~85%. The analytic results demonstrate the power of server caching in application performance.

As stated earlier, if the data in the referenced table undergoes an update, the Cached Result gets invalidated.

SQL> UPDATE EMPLOYEES
     SET SALARY = SALARY+1000
     WHERE EMPLOYEE_ID=160
1 ROW updated.
 
SQL> COMMIT;
Commit complete

Check the status of the above cache id in the V$RESULT_CACHE_OBJECTS view. Now, re-executing the query in cache mode would bring back the ‘Published’ status. Henceforth, it can share the result set on the subsequent executions.

SELECT ID, TYPE, CREATION_TIMESTAMP, STATUS, NAME
FROM V$RESULT_CACHE_OBJECTS
WHERE CACHE_ID = ’94waajh6x9swf27stbrhmf46mt’

        ID TYPE       CREATION_ STATUS    NAME|
———- ———- ——— ——— ———————————————————-
         1 Result     07-APR-11 Invalid   SELECT /*+RESULT_CACHE*/SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID =160

Now, re-executing the query in cache mode would bring back the ‘Published’ status. Henceforth, it can share the result set on the subsequent executions.

SQL> SELECT /*+RESULT_CACHE*/SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 160
    SALARY
----------
      5200
Elapsed: 00:00:00.57

Caching Tips in SQL

The most favorable situations for SQL result caching are when the query execution is repetitive and frequent operation, and the tables contain huge volume of data or the SQL query returns less than 2% of the total data. To get most use of caching, the data is expected to be persistent or change at negligible rate. Interestingly, one must be aware of the fact that result cache uses the most recently committed data and the table must not be in floating state.

Result Cache: Unsupported

Being one of the trump features of Oracle 11g, result cache still provides no support for

• Temporary tables
• Sequences
• Pseudo columns
• Date/Time functions

The reason is the above listed objects are involuntarily randomly changing objects, for which caching can of no use.

PL/SQL Function Result Cache

Oracle 11g can cache the results returned by the PL/SQL stored functions. A stored function can be enabled for result cache by including a new clause RESULT_CACHE to the function definition. The new clause directs the server to cache the function output upon the first call. In the subsequent calls of the function, for the same set of input parameters involved, the result would be retrieved from the server cache without executing the function body.

Note that, the function result is cached along with the set of input parameters. If the function is invoked with a different set of parameters, server would execute the complete function body to arrive at the output. Therefore, it implies that PL/SQL cached result can be uniquely identified as function output, acquainted with the input parameters.

Syntax

CREATE OR REPLACE FUNCTION [FUNCTION NAME]
    RETURN [RETURN TYPE]     
    RESULT_CACHE 
    RELIES ON [TABLE NAME]
    AS
BEGIN
    …
    …
END;

In the syntax, RESULT_CACHE is a mandatory clause to enable result caching for the function. RELIES_ON is an optional clause, which was introduced in Oracle 11g R1to include the names of dependent tables and views. Oracle 11g Release 2 has lifted this restriction by making the clause obsolete.

PL/SQL result cache can be enabled by setting the initialization parameters, exactly in the same manner, as described earlier,. I shall demonstrate the PL/SQL result cache with the help of an illustration.

Illustration 2:

A function F_GET_SAL returns the salary of the employee, whose employee id is the input to the function.

CREATE OR REPLACE FUNCTION F_GET_SAL (P_EMPID NUMER)
    RETURN NUMBER
    RESULT_CACHE
    RELIES ON (EMPLOYEES) IS
    L_SALARY NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE(‘F_GET_SAL called – No cache till yet’);
 
    SELECT SALARY
    INTO L_SALARY
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID=P_EMPID;
 
RETURN L_SALARY;
END F_GET_SAL;

Now, executing the above function for the employee id 160

SQL>SET SERVEROUTPUT ON
SQL>SET TIMING ON
SQL>DBMS_OUTPUT.PUT_LINE(F_GET_SAL(160));
 
F_GET_SAL called – No cache till yet
4200 
 
PL/SQL PROCEDURE successfully completed. 
 
Elapsed: 00.00.05.12

During this execution, the function is called, executed and server caches the result in the cache memory. Now, we shall see what happens in the second execution.

SQL>DBMS_OUTPUT.PUT_LINE(F_GET_SAL(160));
4200 
 
PL/SQL PROCEDURE successfully completed. 
 
Elapsed: 00.00.00.03

Note the function has not been called and the result appears same as earlier. This time the result comes from the server cache, and not from the function. Amazingly, the execution time difference has improved by ~90%.

If the same function is invoked with a different new employee id, oracle re-executes the function to get the output.

SQL>DBMS_OUTPUT.PUT_LINE(F_GET_SAL(130));
F_GET_SAL called – No cache till yet
2300 
 
PL/SQL PROCEDURE successfully completed.
 
Elapsed: 00.00.04.89

PL/SQL Result Cache: Unsupported

Likewise SQL, PL/SQL result caching feature follows some restrictions. PL/SQL result caching feature cannot be enabled for a function with invoker’s rights or a pipelined table function. The function must not have call by value parameters and the IN parameters of LOB, ref cursor or Collection type. The return type of the function must be of a primitive data type (VARCHAR2, NUMBER, or DATE). 

[catlist id=185].