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.

This chapter contains the following topics:

See Also:

Oracle Database Performance Tuning Guide, for more information about “Result Cache Concepts”
Oracle Database PL/SQL Language Reference, for more information about PL/SQL function Result Cache “Using the Cross-Session PL/SQL Function Result Cache”
Database PL/SQL Language Reference, for more information about Result Cache Management.

DBMS_RESULT_CACHE Security Model

Only database administrators should be granted the EXECUTE privilege for this package.

DBMS_RESULT_CACHE Constants

The DBMS_RESULT_CACHE package defines several constants for specifying parameter values.

The following table describes these constants.

Table 1 DBMS_RESULT_CACHE Constants

Constant	Definition	Description
`STATUS_BYPS`	`CONSTANT VARCHAR2(10) := 'BYPASS';`	Cache has been made temporarilyunavailable.
`STATUS_CORR`	`CONSTANT VARCHAR2(10) := 'CORRUPT';`	The result cache is in an unusable state.
`STATUS_DISA`	`CONSTANT VARCHAR2(10) := 'DISABLED';`	Cache is not available.
`STATUS_ENAB`	`CONSTANT VARCHAR2(10) := 'ENABLED';`	Cache is available.
`STATUS_SYNC`	`CONSTANT VARCHAR2(10) := 'SYNC';`	Cache is available, but synchronizing with Oracle RAC nodes.

Summary of DBMS_RESULT_CACHE Subprograms

This table lists the DBMS_RESULT_CACHE subprograms and briefly describes them.

Table 2 DBMS_RESULT_CACHE Package Subprograms

Subprogram	Description
BLOCK_LIST Function	Returns all the block listed cache ids of a local instance.
BLOCK_LIST_ADD Procedure	Adds a `cache_id` to the block list.
BLOCK_LIST_CLEAR Procedure	Removes all cache_ids from the block list.
BLOCK_LIST_REMOVE Procedure	Removes the `cache_id` from the block list.
BYPASS Procedure	Sets the bypass mode for the result cache.
DELETE_DEPENDENCY Function & Procedure	Deletes the specified dependency object from 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.
IGNORE_LIST Function	This table function returns a row for each entry that’s been added into the ignore list.
IGNORE_LIST_ADD Procedure	This procedure adds to the ignore list a table specified by object number or by owner and table name.
IGNORE_LIST_CLEAR Procedure	This procedure drops the entire ignore list.
IGNORE_LIST_REMOVE Procedure	This procedure removes a table specified by object number or by owner and table name.
INVALIDATE Functions & Procedures	Invalidates all the result-set objects that are 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.
OBJECT_BLOCK_LIST Function	Returns all the object numbers currently in the block list
OBJECT_BLOCK_LIST_ADD Procedure	Adds objects to the object block list
OBJECT_BLOCK_LIST_CLEAR Procedure	Clears the entire block list
OBJECT_BLOCK_LIST_REMOVE Procedure	Removes objects from the object block list.
STATUS Function	Checks the status of the result cache.

BLACK_LIST Function

This procedure is deprecated with Oracle AI Database 26ai. Use the BLOCK_LIST Function instead.

BLACK_LIST_ADD Procedure

This procedure is deprecated with Oracle AI Database 26ai. Use the BLOCK_LIST_ADD Procedure instead.

BLACK_LIST_CLEAR Procedure

This procedure is deprecated with Oracle AI Database 26ai. Use the BLOCK_LIST_CLEAR Procedure instead.

BLACK_LIST_REMOVE Procedure

This procedure is deprecated with Oracle AI Database 26ai. Use the BLOCK_LIST_REMOVE Procedure instead.

BYPASS Procedure

This procedure sets the bypass mode for the Result Cache.

It sets one of the following bypass modes:

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,
   session        IN   BOOLEAN);

Parameters

Table 3 BYPASS Procedure Parameters

Parameter	Description
`bypass_mode`	`TRUE` => Result Cache usage is bypassed `FALSE` => Result Cache usage is turned on
`session`	`TRUE` => Applies to current session `FALSE` (default) => Applies to all sessions

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.

DELETE_DEPENDENCY Functions & Procedures

These functions and procedures delete the specified dependency object from the result cache, while invalidating all results that used that dependency object.

Syntax

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

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

DBMS_RESULT_CACHE.DELETE_DEPENDENCY (
  object_id    IN BINARY_INTEGER)
 RETURN NUMBER;

DBMS_RESULT_CACHE.DELETE_DEPENDENCY (
   object_id    IN BINARY_INTEGER);

Parameters

Table 4 DELETE_DEPENDENCY Function

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

Return Values

The number of objects deleted.

Note: Unlike INVALIDATE, DELETE_DEPENDENCY is used to remove a dependency object from the result cache. For example, when a query result is cached, the tables involved in that query are added to certain result cache data structures. In RAC, every time a transaction commits on those tables, extra processing occurs. If this overhead grows too large, it can cause slowdowns or brownouts. DELETE_DEPENDENCY lets you remove a table from the result cache data structures, stopping the extra work and preventing brownouts.

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,
   global     IN  BOOLEAN DEFAULT FALSE)
  RETURN BOOLEAN;

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

Parameters

Table 5 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
`global`	`TRUE` => flushes all caches in the RAC cluster `FALSE` (default) => flushes only the local instance cache

Return Values

TRUE if successful in removing all the objects.

IGNORE_LIST Function

This table function returns a row for each entry that’s been added into the ignore list.

Objects in the ignore list are never considered for auto block listing. This allows users to override auto block listing.

Syntax

type ign_recT is record( objNo NUMBER);
type ign_tabT is table of ign_recT;
DBMS_RESULT_CACHE.IGNORE_LIST ()
   RETURN ign_tabT pipelined;

IGNORE_LIST_ADD Procedure

This procedure adds to the ignore list a table specified by object number or by owner and table name.

Objects in the ignore list are never considered for auto block listing. This allows users to override auto block listing.

Syntax

DBMS_RESULT_CACHE.IGNORE_LIST_ADD (
   objNo   IN  NATURALN);

Syntax

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

Parameters

Table 6 IGNORE_LIST_ADD Procedure Parameters

Parameter	Description
`objNo`	The `objNo` is added to the ignore list.
`name`	The table specified by `owner` and `name` are added to the ignore list.
`owner`	The table specified by `owner` and `name` are added to the ignore list.

IGNORE_LIST_CLEAR Procedure

This procedure drops the entire ignore list.

Objects in the ignore list are never considered for auto block listing. This allows users to override auto block listing.

Syntax

DBMS_RESULT_CACHE.IGNORE_LIST_CLEAR;

IGNORE_LIST_REMOVE Procedure

This procedure removes a table specified by object number or by owner and table name.

Syntax

DBMS_RESULT_CACHE.IGNORE_LIST_REMOVE (
   objNo   IN  NATURALN);

Syntax

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

Parameters

Table 7 IGNORE_LIST_REMOVE Procedure Parameters

Parameter	Description
`objNo`	The `objNo` is removed from the ignore list.
`name`	The table specified by `owner` and `name` is removed to the ignore list.
`owner`	The table specified by `owner` and `name` is removed to the ignore list.

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 8 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 9 INVALIDATE_OBJECT Function & Procedure Parameters

Parameter	Description
`id`	Address of the cache object in the Result Cache
`cache_id`	Result cache identifier of a SQL cursor or PL/SQL function.

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 10 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.

Examples

SET SERVEROUTPUT ON

EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;

OBJECT_BLACK_LIST Function

This function is deprecated with Oracle AI Database 26ai. Use the OBJECT_BLOCK_LIST Function instead.

OBJECT_BLACK_LIST_ADD Procedure

This procedure is deprecated with Oracle AI Database 26ai. Use the OBJECT_BLOCK_LIST_ADD Procedure instead.

OBJECT_BLACK_LIST_CLEAR Procedure

This procedure is deprecated with Oracle AI Database 26ai. Use the OBJECT_BLOCK_LIST_CLEAR Procedure instead.

OBJECT_BLACK_LIST_REMOVE Procedure

This procedure is removed with Oracle AI Database 26ai. Use OBJECT_BLOCK_LIST_REMOVE Procedure instead.

STATUS Function

This function checks the status of the Result Cache.

Syntax

DBMS_RESULT_CACHE.STATUS
   RETURN VARCHAR2;

Note: For more information on the constants, see DBMS_RESULT_CACHE Constants.

BLOCK_LIST Function

This table function returns all the block-listed cache ids of a local instance.

Syntax

DBMS_RESULT_CACHE.BLOCK_LIST
   RETURN BL_TABT;

BLOCK_LIST_ADD Procedure

This procedure adds a cache_id to the block list.

Syntax

DBMS_RESULT_CACHE.BLOCK_LIST_ADD (
   cache_id   IN  VARCHAR2,
   global     IN  BOOLEAN DEFAULT FALSE);

Parameters

Table 11 BLOCK_LIST_ADD Procedure Parameters

Parameter Description

cache_id The cache_id is added to the block list.

Parameter	Description
`cache_id`	The `cache_id` is added to the block list.
`global`	`TRUE` - applies to all caches in a RAC cluster. `FALSE` (default) - applies only to the local instance cache.

global

TRUE - applies to all caches in a RAC cluster.

FALSE (default) - applies only to the local instance cache.

BLOCK_LIST_CLEAR Procedure

This procedure removes all cache_ids from the block list.

Syntax

DBMS_RESULT_CACHE.BLOCK_LIST_CLEAR (
   global   IN  BOOLEAN DEFAULT FALSE);

Parameters

Table 12 BLOCK_LIST_CLEAR Procedure Parameters

Parameter Description

Parameter	Description
`global`	`TRUE` - applies to all caches in a RAC cluster. `FALSE` (default) - applies only to the local instance cache.

global

TRUE - applies to all caches in a RAC cluster.

FALSE (default) - applies only to the local instance cache.

BLOCK_LIST_REMOVE Procedure

This procedures removes the cache_id from the block list.

Syntax

DBMS_RESULT_CACHE.BLOCK_LIST_REMOVE (
   cache_id   IN  VARCHAR2,
   global     IN  BOOLEAN DEFAULT FALSE);

Parameters

Table 13 BLOCK_LIST_REMOVE Procedure Parameters

Parameter Description

cache_id The cache_id is removed from the block list.

Parameter	Description
`cache_id`	The `cache_id` is removed from the block list.
`global`	`TRUE` - applies to all caches in a RAC cluster. `FALSE` (default) - applies only to the local instance cache.

global

TRUE - applies to all caches in a RAC cluster.

FALSE (default) - applies only to the local instance cache.

OBJECT_BLOCK_LIST Function

This table function returns all the object numbers currently in the block list.

Syntax

DBMS_RESULT_CACHE.OBJECT_BLOCK_LIST ()
   RETURN NATURALN;

OBJECT_BLOCK_LIST_ADD Procedure

This procedures adds objects to the object block list. These procedures only have to be run once in the cluster as they take effect everywhere at once. However, they do not persist between cluster shutdowns and startups.

Syntax

DBMS_RESULT_CACHE.OBJECT_BLOCK_LIST_ADD (
   objNo       IN  BINARY_INTEGER);

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

Parameters

Table 14 OBJECT_BLOCK_LIST_ADD Procedure Parameters

Parameter	Description
`objNo`	The object number.
`owner`	The owner of the object.
`name`	The name of the table.

OBJECT_BLOCK_LIST_CLEAR Procedure

This procedure clears the entire block list.

Syntax

DBMS_RESULT_CACHE.OBJECT_BLOCK_LIST_CLEAR ();

OBJECT_BLOCK_LIST_REMOVE Procedure

This procedures removes objects from the object block list.

Syntax

DBMS_RESULT_CACHE.OBJECT_BLOCK_LIST_REMOVE (
   objNo       IN  BINARY_INTEGER);

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

Parameters

Table 15 OBJECT_BLOCK_LIST_REMOVE Procedure Parameters

Parameter	Description
`objNo`	The object number.
`owner`	The owner of the object.
`name`	The name of the table.