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.
Captures the server cache performance stats, including block count and create count values.
Captures the server cache memory stats
Captures the cached result sets information, including status
Captures the dependencies of a result cache
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.
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
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
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
- 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.
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.
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).