Consider an application that has configuration parameters that can be set at either the global level, the application level, or the role level. The application stores the configuration information in these tables:

-- Global Configuration Settings
DROP TABLE global_config_params;
CREATE TABLE global_config_params
  (name  VARCHAR2(20), -- parameter NAME
   val   VARCHAR2(20), -- parameter VALUE
   PRIMARY KEY (name)
  );

-- Application-Level Configuration Settings
CREATE TABLE app_level_config_params
  (app_id  VARCHAR2(20), -- application ID
   name    VARCHAR2(20), -- parameter NAME
   val     VARCHAR2(20), -- parameter VALUE
   PRIMARY KEY (app_id, name)
  );

-- Role-Level Configuration Settings
CREATE TABLE role_level_config_params
  (role_id  VARCHAR2(20), -- application (role) ID
   name     VARCHAR2(20),  -- parameter NAME
   val      VARCHAR2(20),  -- parameter VALUE
   PRIMARY KEY (role_id, name)
  );

For each configuration parameter, the role-level setting overrides the application-level setting, which overrides the global setting. To determine which setting applies to a parameter, the application defines the PL/SQL function get_value. Given a parameter name, application ID, and role ID, get_value returns the setting that applies to the parameter.

The function get_value is a good candidate for result-caching if it is invoked frequently and if the configuration information changes infrequently.

Example 9-40 shows a possible definition for get_value. Suppose that for one set of parameter values, the global setting determines the result of get_value. While get_value is running, the database detects that three tables are queried—role_level_config_params, app_level_config_params, and global_config_params. If a change to any of these three tables is committed, the cached result for this set of parameter values is invalidated and must be recomputed.

Now suppose that, for a second set of parameter values, the role-level setting determines the result of get_value. While get_value is running, the database detects that only the role_level_config_params table is queried. If a change to role_level_config_params is committed, the cached result for the second set of parameter values is invalidated; however, committed changes to app_level_config_params or global_config_params do not affect the cached result.

CREATE OR REPLACE FUNCTION get_value
  (p_param VARCHAR2,
   p_app_id  NUMBER,
   p_role_id NUMBER
  )
  RETURN VARCHAR2
  RESULT_CACHE
  AUTHID DEFINER
IS
  answer VARCHAR2(20);
BEGIN
  -- Is parameter set at role level?
  BEGIN
    SELECT val INTO answer
      FROM role_level_config_params
        WHERE role_id = p_role_id
          AND name = p_param;
    RETURN answer;  -- Found
    EXCEPTION
      WHEN no_data_found THEN
        NULL;  -- Fall through to following code
  END;
  -- Is parameter set at application level?
  BEGIN
    SELECT val INTO answer
      FROM app_level_config_params
        WHERE app_id = p_app_id
          AND name = p_param;
    RETURN answer;  -- Found
    EXCEPTION
      WHEN no_data_found THEN
        NULL;  -- Fall through to following code
  END;
  -- Is parameter set at global level?
    SELECT val INTO answer
     FROM global_config_params
      WHERE name = p_param;
    RETURN answer;
END;
/

A recursive function for finding the nth term of a Fibonacci series that mirrors the mathematical definition of the series might do many redundant computations. For example, to evaluate fibonacci(7), the function must compute fibonacci(6) and fibonacci(5). To compute fibonacci(6), the function must compute fibonacci(5) and fibonacci(4). Therefore, fibonacci(5) and several other terms are computed redundantly. Result-caching avoids these redundant computations.

CREATE OR REPLACE FUNCTION fibonacci (n NUMBER)
  RETURN NUMBER
  RESULT_CACHE
  AUTHID DEFINER
IS
BEGIN
  IF (n =0) OR (n =1) THEN
    RETURN 1;
  ELSE
    RETURN fibonacci(n - 1) + fibonacci(n - 2);
  END IF;
END;
/

Each time a result-cached function is invoked with different parameter values, those parameters and their result are stored in the cache. Subsequently, when the same function is invoked with the same parameter values (that is, when there is a cache hit), the result is retrieved from the cache, instead of being recomputed.

The rules for parameter comparison for a cache hit differ from the rules for the PL/SQL "equal to" (=) operator, as follows:

In some situations, the cache is bypassed. When the cache is bypassed:

• The function computes the result instead of retrieving it from the cache.

• The result that the function computes is not added to the cache.

Some examples of situations in which the cache is bypassed are:

• The cache is unavailable to all sessions.

  For example, the database administrator has disabled the use of the result cache during application patching (as in "Hot-Patching PL/SQL Units on Which Result-Cached Functions Depend").

• A session is performing a DML statement on a table or view on which a result-cached function depends.

  The session bypasses the result cache for that function until the DML statement is completed—either committed or rolled back. If the statement is rolled back, the session resumes using the cache for that function.

  Cache bypass ensures that:

  • The user of each session sees their own uncommitted changes.

  • The PL/SQL function result cache has only committed changes that are visible to all sessions, so that uncommitted changes in one session are not visible to other sessions.

Cached results are stored in the system global area (SGA). In an Oracle RAC environment, each database instance manages its own local function result cache. However, the contents of the local result cache are accessible to sessions attached to other Oracle RAC instances. If a required result is missing from the result cache of the local instance, the result might be retrieved from the local cache of another instance, instead of being locally computed. The access pattern and workload of an instance determine the set of results in its local cache; therefore, the local caches of different instances can have different sets of results.

Before fetching a cached result from a remote instance, the database uses heuristics to determine if it is more cost efficient to recompute the result on the local instance. You can monitor the use of this functionality by querying the V$RESULT_CACHE_OBJECTS and V$RESULT_CACHE_STATISTICS views. The V$RESULT_CACHE_OBJECTS has a value ’Yes’ in the GLOBAL column if the object has been fetched from the result cache of another instance. A value of ’No’ means that the result was locally recomputed, either because it was not available remotely, or because the system has decided it is more efficient to do so instead of fetching it remotely. The statistics ’Global Prune Count’ in the V$RESULT_CACHE_STATISTICS view shows the number of times the decision was made not to fetch from a remote instance. ’Global Prune By Self Count’ shows the number of times an instance asked to provide a local result and has decided it is more efficient for the requesting instance to compute the result locally. Finally, ’Global Load Rate’ shows the computed rate - in bytes per 10 milliseconds - of fetching results from result cache of other instances. All these statistics only apply to global result caches in a RAC environment.

Although each database instance might have its own set of cached results, the mechanisms for handling invalid results are Oracle RAC environment-wide. For example, consider a result cache of item prices that are computed from data in database tables. If any of these database tables is updated in a way that affects the price of an item, the cached price of that item is invalidated in every database instance in the Oracle RAC environment.

The PL/SQL function result cache shares its administrative and manageability infrastructure with the Result Cache.

You can administer the shared pool area part that is used by the SQL result cache and the PL/SQL function result cache using the DBMS_RESULT_CACHE subprograms. Using the DBMS_RESULT_CACHE.BLACKLIST_ADD procedure, you can add a query or a PL/SQL function to a blocklist to stop caching the results. No matter the bind variables or arguments used, there will be no objects generated for it. The result cache row source may still appear in the explain plan, but at runtime it will be a no-op. You can solve a result cache issue if you diagnose by looking for a case when ten of thousands set of cached results unique arguments is run for a function. Depending on the workload, the overhead of managing these cached results might offset the benefits of caching the results. The performance views gives you insight on this special cases.

You can run a query to identify problematic queries or functions. The cache_id is the result cache identifier of a SQL cursor or PL/SQL function. This query counts how many unique result cache objects were made for each cache id. A unique object is created for every run of a query or function with unique bind variables or arguments.

SELECT cache_id, COUNT(cache_key) AS uniq_args
FROM GV$RESULT_CACHE_OBJECTS
WHERE type = 'Result'
GROUP BY cache_id
ORDER BY uniq_args DESC;

When a dependent object is frequently updated by a workload, it can adversely impact the performance benefits of using result cache. For example, when a large transaction is committed and is affecting already cached results, messages are sent to invalidate these cached results to prevent wrong results. The first hint that this bottleneck is happening is the observation of high waits with CHANNEL = 'Result Cache: Channel' in the GV$CHANNEL_WAITS view. You can run a query to check the culprit and take appropriate action such as adding the object to the blocklist. An object with an extremely high number of invalidations can be diagnosed using this query.

SELECT object_no, SUM(invalidations) AS num_invals
FROM GV$RESULT_CACHE_OBJECTS
WHERE type = 'Dependency'
GROUP BY object_no
ORDER BY num_invals DESC;

Dynamic performance views provide information to monitor the server and client result caches.

The database administrator manages the server result cache by specifying the result cache initialization parameters.

1. Put the result cache in bypass mode and flush existing results:

   BEGIN
     DBMS_RESULT_CACHE.Bypass(TRUE);
     DBMS_RESULT_CACHE.Flush;
   END;
   /

   In an Oracle RAC environment, perform this step for each database instance.

2. Patch the PL/SQL code.
3. Resume using the result cache:

   BEGIN
     DBMS_RESULT_CACHE.Bypass(FALSE);
   END;
   /

   In an Oracle RAC environment, perform this step for each database instance.