In Oracle 11g, there is a new SGA component called result cache, which is used cache SQL query and PL/SQL function results.
The database serves the results for the executed SQL queries and PL/SQL functions from the cache instead of re-executing the actual query. Of course, the target is to obtain high response time.
The cached results stored become invalid when data in the dependent database objects is modified.
As clear from its concept, result cache is mostly useful in for frequently executed queries with rare changes on the retrieved data.
Result Cache Restrictions
Following are some restrictions with regards to the SQL result cache:
1.Queries against data dictionary objects and temporary tables are not supported.
2.Queries that use the following SQL functions: CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT, SYS_GUID, SYSDATE and SYS_TIMESTAMP are not supported.
3.Queries with bind variables can reuse a cached result only for identical variable values.
4.Results of the queries retrieving non current version of data are not cached in the result cache.
5.Restrictions on PL/SQL Function Result Cache include:
*.The function cannot be defined in a module using invoker’s rights.
*.The function cannot be used in an anonymous block.
*.The function cannot have any OUT or IN OUT parameters.
*.The function cannot have IN parameters that are BLOB, CLOB, NCLOB, REF CURSOR, collections, objects, or records.
*.The function cannot return a BLOB, CLOB, NCLOB, REF CURSOR, OBJECTS, or records. It can return a collection as long as the collection does not contain one of these types.
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:
Default Percentage of Shared Pool to Result Cache
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 */
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;
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)
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.
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.
2. Apply the PL/SQL code patches.
3. Resume use of the result cache, by turning off the cache bypass mode.
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
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
-- turn bypass mode on and off
SQL>exec DBMS_RESULT_CACHE.BYPASS (TRUE);
-- to flush the result cache