1. PL/SQL Packages and Types Reference
  2. DBMS_RESULT_CACHE

154 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:

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

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 Database 23ai. Use the BLOCK_LIST Function instead.

BLACK_LIST_ADD Procedure

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

BLACK_LIST_CLEAR Procedure

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

BLACK_LIST_REMOVE Procedure

This procedure is deprecated with Oracle Database 23ai. 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 154-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:

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

  2. Apply the PL/SQL code patches.

  3. 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 154-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.

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 154-5 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 154-6 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 154-7 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 154-8 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 154-9 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 Database 23ai. Use the OBJECT_BLOCK_LIST Function instead.

OBJECT_BLACK_LIST_ADD Procedure

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

OBJECT_BLACK_LIST_CLEAR Procedure

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

OBJECT_BLACK_LIST_REMOVE Procedure

This procedure is removed with Oracle Database 23ai. 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 154-10 BLOCK_LIST_ADD Procedure Parameters

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.

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 154-11 BLOCK_LIST_CLEAR Procedure Parameters

Parameter Description

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 154-12 BLOCK_LIST_REMOVE Procedure Parameters

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.

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 154-13 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 154-14 OBJECT_BLOCK_LIST_REMOVE Procedure Parameters

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