107 DBMS_RESULT_CACHE

The DBMS_RESULT_CACHE package provides an interface to allow the DBA to administer that part of the shared pool that is used by the SQL result cache and the PL/SQL function result cache. Both these caches use the same infrastructure. Therefore, for example, DBMS_RESULT_CACHE.BYPASS determines whether both caches are bypassed or both caches are used, and DBMS_RESULT_CACHE.FLUSH flushes both all the cached results for SQL queries and all the cached results for PL/SQL functions.

Using DBMS_RESULT_CACHE

Constants

Constants

Table 107-1 DBMS_RESULT_CACHE Constants

Constant	Definition
`STATUS_CLSD`	`CONSTANT VARCHAR(10) := 'CLOSED';`
`STATUS_OPEN`	`CONSTANT VARCHAR(10) := 'OPEN';`
`STATUS_SYNC`	`CONSTANT VARCHAR(10) := 'SYNC';`

Summary of DBMS_RESULT_CACHE Subprograms

Table 107-2 DBMS_RESULT_CACHE Package Subprograms

Subprogram	Description
BYPASS Procedure	Sets the bypass mode for the Result Cache
FLUSH Function & Procedure	Attempts to remove all the objects from the Result Cache, and depending on the arguments retains or releases the memory and retains or clears the statistics
INVALIDATE Functions & Procedures	Invalidates all the result-set objects that dependent upon the specified dependency object
INVALIDATE_OBJECT Functions & Procedures	Invalidates the specified result-set object(s)
MEMORY_REPORT Procedure	Produces the memory usage report for the Result Cache
STATUS Function	Checks the status of the Result Cache

BYPASS Procedure

This procedure sets the bypass mode for the Result Cache:

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.
When bypass mode is turned off, the cache resumes normal operation.

Syntax

DBMS_RESULT_CACHE.BYPASS (
   bypass_mode IN BOOLEAN);

Parameters

Table 107-3 BYPASS Procedure Parameters

Parameter	Description
`bypass_mode`	`TRUE` => Result Cache usage is bypassed `FALSE` => Result Cache usage is turned on

Usage Notes

This operation is database instance specific.

Examples

This operation can be used when there is a need to hot patch PL/SQL code in a running system. If a code-patch is applied 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 (if the instance is not restarted/bounced). This must be manually achieved.

To ensure correctness during the patching process follow these steps:

Place the result cache in bypass mode, and flush existing result.
```
BEGIN
   DBMS_RESULT_CACHE.BYPASS(TRUE);
   DBMS_RESULT_CACHE.FLUSH;
END;
/
```
This step must be performed on each instance if in a Oracle Real Application Clusters environment.
Apply the PL/SQL code patches.
Resume use of the result cache, by turning off the cache bypass mode.
```
BEGIN
   DBMS_RESULT_CACHE.BYPASS(FALSE);
END;
/
```
This step must be performed on each instance if in a Oracle Real Application Clusters environment.

FLUSH Function & Procedure

This function and procedure attempts to remove all the objects from the Result Cache, and depending on the arguments retains or releases the memory and retains or clears the statistics.

Syntax

DBMS_RESULT_CACHE.FLUSH (
   retainMem  IN  BOOLEAN DEFAULT FALSE,
   retainSta  IN  BOOLEAN DEFAULT FALSE) 
  RETURN BOOLEAN;

DBMS_RESULT_CACHE.FLUSH (
   retainMem  IN  BOOLEAN DEFAULT FALSE,
   retainSta  IN  BOOLEAN DEFAULT FALSE);

Parameters

Table 107-4 FLUSH Function & Procedure Parameters

Parameter	Description
`retainMem`	`TRUE` => retains the free memory in the cache `FALSE` (default) => releases the free memory to the system
`retainSta`	`TRUE` => retains the existing cache statistics `FALSE` (default) => clears the existing cache statistics

Return Values

TRUE if successful in removing all the objects.

INVALIDATE Functions & Procedures

This function and procedure invalidates all the result-set objects that dependent upon the specified dependency object.

Syntax

DBMS_RESULT_CACHE.INVALIDATE (
   owner        IN  VARCHAR2, 
   name         IN  VARCHAR2) 
 RETURN NUMBER;

DBMS_RESULT_CACHE.INVALIDATE (
   owner       IN  VARCHAR2, 
   name        IN  VARCHAR2);

DBMS_RESULT_CACHE.INVALIDATE (
  object_id    IN BINARY_INTEGER) 
 RETURN NUMBER;

DBMS_RESULT_CACHE.INVALIDATE (
   object_id    IN BINARY_INTEGER);

Parameters

Table 107-5 INVALIDATE Function & Procedure Parameters

Parameter	Description
`owner`	Schema name
`name`	Object name
`object_id`	Dictionary object number

Return Values

The number of objects invalidated.

INVALIDATE_OBJECT Functions & Procedures

This function and procedure invalidates the specified result-set object(s).

Syntax

DBMS_RESULT_CACHE.INVALIDATE_OBJECT (
   id          IN  BINARY_INTEGER) 
 RETURN NUMBER;

DBMS_RESULT_CACHE.INVALIDATE_OBJECT (
   id          IN  BINARY_INTEGER);

DBMS_RESULT_CACHE.INVALIDATE_OBJECT (
  cache_id     IN  VARCHAR2) 
 RETURN NUMBER;

DBMS_RESULT_CACHE.INVALIDATE_OBJECT (
   cache_id   IN  VARCHAR2);

Parameters

Table 107-6 INVALIDATE Function & Procedure Parameters

Parameter	Description
`id`	Address of the cache object in the Result Cache
`cache_id`	Cache-id

Return Values

The number of objects invalidated.

MEMORY_REPORT Procedure

This procedure produces the memory usage report for the Result Cache.

Syntax

DBMS_RESULT_CACHE.MEMORY_REPORT (
   detailed   IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 107-7 MEMORY_REPORT Procedure Parameters

Parameter	Description
`detailed`	`TRUE` => produces a more detailed report `FALSE` (default) => produces the standard report

Usage Notes

Invoking this procedure from SQL*Plus requires that the serveroutput be turned on.

STATUS Function

This function checks the status of the Result Cache.

Syntax

DBMS_RESULT_CACHE.STATUS
   RETURN VARCHAR2;

Return Values

One of the following values:

STATUS_CLSD - Cache is not available
STATUS_OPEN - Cache is available
STATUS_SYNC - Cache is available, but synchronizing with RAC nodes