Oracle 11g Server Result Cache

Configuring Result Cache
You can enable and disable result cache in the database server using the parameter RESULT_CACHE_MAX_SIZE.  This parameter specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache. If the value of this parameter is 0, then the feature is disabled.

Memory allocated for the result cache is taken from the shared pool.
The default value of RESULT_CACHE_MAX_SIZE parameter is derived from values of other parameters and as shown in the following table:
Parameter
Default Percentage of Shared Pool to Result Cache
MEMORY_TARGET 0.25%
SGA_TARGET 0.5%
SHARED_POOL_SIZE 1%

Of course, you can increase value of the RESULT_CACHE_MAX_SIZE parameter but in all cases Oracle does not allocate more than 75 percent of shared pool memory to result cache.

ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE =128M;

Note : RESULT_CACHE_MAX_SIZE cannot be dynamically changed if its value is set to 0 during database startup.

Controlling Result Cache Behavior
The RESULT_CACHE_MODE initialization parameter determines the SQL query result cache mode. The parameter specifies when a ResultCache operator is spliced into a query’s execution plan.
The parameter accepts the following values:
MANUAL : The ResultCache operator is added, only if you use the RESULT_CACHE hint in the SQL query.
FORCE : The ResultCache operator is added to the root of all SELECT statements, if that is possible. However, if the statement contains a NO_RESULT_CACHE hint, then the hint takes precedence over the parameter setting.

The parameter can be modified in the system or session level.
ALTER SYSTEM SET RESULT_CACHE_MODE =FORCE;
ALTER SESSION SET RESULT_CACHE_MODE =FORCE;

Following is an example of using the RESULT_CACHE hint.
SELECT /*+ result_cache */
AVG(SALARY), E.DEPARTMENT_ID
FROM HR.EMPLOYEES E, HR.DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID GROUP BY E.DEPARTMENT_ID;
The parameter RESULT_CACHE_MAX_RESULT specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use. Its default value is five.

ALTER SYSTEM SET RESULT_CACHE_MAX_RESULT =25;

The parameter RESULT_CACHE_REMOTE_EXPIRATION specifies the number of minutes that a result using a remote object is allowed to remain valid.

Setting this parameter to 0 (the default) implies that results using remote objects should not be cached.
Setting this parameter to a non-zero value can produce stale answers.

PL/SQL Function Result Cache

When PL/SQL function result cache is enabled,
Oracle will check the result cache to see if a previous call to the function exists (using the same parameter passed values) and if so it will return the cached result instead of executing the function.
A PL/SQL function can take advantage of the PL/SQL Function Result Cache by adding the RESULT_CACHE clause to the function definition.
In the optional RELIES_ON clause, specify any tables or views on which the function results depend.

Following is an example:
CREATE OR REPLACE FUNCTION get_name (id NUMBER) RETURN VARCHAR2
RESULT_CACHE RELIES_ON(emp) IS …
— Package specification
CREATE OR REPLACE PACKAGE department_pks IS
— Function declaration
FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record RESULT_CACHE;
END department_pks;
CREATE OR REPLACE PACKAGE BODY department_pks AS
— Function definition
FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record
RESULT_CACHE RELIES_ON (EMPLOYEES)
IS
BEGIN …
If there is a dependent table in a function and that table was modified, then the result cache will be invalidated (cache miss).
In this case, the function will be re-executed when called. The same will also occur if you re-compile the function.

Caution
When a session reads from a PL/SQL function result cache, the function body is not executed. This means, if the function includes any IO or auditing code, this code will not actually be executed.
PL/SQL Cached functions works across sessions. This means, if the function is cached by a session, its result cache will also be used when executing the same function and arguments by other sessions.
If you need to apply a hot patch PL/SQL code in a running system to a PL/SQL module on which a result cached function directly or transitively depends, then the cached results associated with the result cache function are not automatically flushed. In this case, the following steps must be undertaken:

1. Place the result cache in bypass mode, and flush existing result. When bypass mode is turned on, it implies that cached results are no longer used and that no new results are saved in the cache.

begin
DBMS_RESULT_CACHE.BYPASS(TRUE);
DBMS_RESULT_CACHE.FLUSH;
end;
2. Apply the PL/SQL code patches.
3. Resume use of the result cache, by turning off the cache bypass mode.
exec DBMS_RESULT_CACHE.BYPASS(FALSE);

Monitoring Result Cache
If you display the explain plan for any query with a RESULT_CACHE hint,
you will notice the ResultCache operator. You can use the CACHE_ID value provided in the
explain plan to find details about the cached query results using the V$RESULT_CACHE_OBJECTS view, as shown in the following example:
select NAME, STATUS, ROW_COUNT, BLOCK_COUNT, NAMESPACE,
to_char(CREATION_TIMESTAMP,’HH12:MI AM’) CREATE_TIME
from V$RESULT_CACHE_OBJECTS
where CACHE_ID=’ctpgzz1qb222tfqw61j203h01b’;
Following are the possible values for the STATUS column and their descriptions:
NEW : Result is still under construction
PUBLISHED : Result is available for use
BYPASS : Result will be bypassed from use
EXPIRED : Result has exceeded expiration time
INVALID : Result is no longer available for use

V$RESULT_CACHE_STATISTICS view provides information and statistics on cache settings and memory usage.

select ID, NAME, VALUE from V$RESULT_CACHE_STATISTICS
The NAME column possible values are as follows:Block Size : (Bytes) size of each memory block
Block Count : Maximum number of memory blocks allowed
Block Count : Current Number of memory blocks currently allocated
Result Size : Maximum (Blocks) Maximum number of blocks allowed for a single result
Create Count : Success Number of cache results successfully created
Create Count : Failure Number of cache results that failed to create
Find Count : Number of cached results that were successfully found
Invalidation : Count Total number of invalidations
Delete Count : Invalid Number of invalid cached results deleted
Delete Count : Valid Number of valid cached results deleted

The V$RESULT_CACHE_MEMORY view displays all the memory blocks and their status. Of course, number of rows in this view increases as the result cache is enlarged by its usage.

The V$RESULT_CACHE_DEPENDENCY view displays the depends-on relationship between the cached results objects.Monitoring and Managing Result Cache with DBMS_RESULT_CACHE

Beside the dictionary views, the package DBMS_RESULT_CACHE can also be used to monitor and manage result cache usage.
Below are some examples of using it:
— check the status of the Result Cache
— Note: this is the reliable method to know whether result cache is enabled or not
SQL>select DBMS_RESULT_CACHE.STATUS from dual;
— display report on result cache memory
SQL>set serveroutput on
SQL>exec DBMS_RESULT_CACHE.MEMORY_REPORT
— turn bypass mode on and off
SQL>exec DBMS_RESULT_CACHE.BYPASS (TRUE);
— to flush the result cache
SQL>exec DBMS_RESULT_CACHE.FLUSH