Chapter 28. DBMS_RESULT_CACHE

Table of Contents

28.1. Overview
28.2. Procedures and Functions
28.2.1. BYPASS
28.2.2. FLUSH
28.2.3. INVALIDATE
28.2.4. INVALIDATE_OBJECT
28.2.5. MEMORY_REPORT
28.2.6. STATUS

This chapter briefly introduces the DBMS_RESULT_CACHE package, and describes how to use the procedures of the package.

28.1. Overview

DBMS_RESULT_CACHE provides an interface that allow a DBA to control the result cache in the shared memory.

The following are constants defined in the DBMS_RESULT_CACHE package.

  • STATUS_BYPS

    STATUS_BYPS CONSTANT VARCHAR(10) := 'BYPASS'
  • STATUS_DISA

    STATUS_DISA CONSTANT VARCHAR(10) := 'DISABLED'
  • STATUS_ENAB

    STATUS_ENAB CONSTANT VARCHAR(10) := 'ENABLED'

28.2. Procedures and Functions

This section describes the procedures provided by the DBMS_RESULT_CACHE package, in alphabetical order.

28.2.1. BYPASS

Configures the bypass mode of the result cache. When the bypass mode is enabled, saved results can no longer be used and new results are not saved. When the bypass mode is disabled, the result cache can be used.

Details about the BYPASS procedure are as follows:

  • Prototype

    DBMS_RESULT_CACHE.BYPASS 
    (
        bypass_mode     IN      BOOLEAN
    );
  • Parameter

    ParameterDescription
    bypass_mode
    • TRUE: Result cache cannot be used.

    • FALSE: Result cache can be used.

  • Example

    BEGIN
        DBMS_RESULT_CACHE.BYPASS(TRUE);
        DBMS_RESULT_CACHE.FLUSH;
    END;
    /

28.2.2. FLUSH

Attempts to delete (flush) all objects that are saved in the result cache, but some objects may fail to be deleted.

Details about the FLUSH function and procedure are as follows:

  • Prototype

    • Function

      DBMS_RESULT_CACHE.FLUSH 
      (
          retain_mem      IN      BOOLEAN DEFAULT FALSE,
          retain_stat     IN      BOOLEAN DEFAULT FALSE
      ) 
      RETURN BOOLEAN;
    • Procedure

      DBMS_RESULT_CACHE.FLUSH 
      (
          retain_mem      IN      BOOLEAN DEFAULT FALSE,
          retain_stat     IN      BOOLEAN DEFAULT FALSE
      )
  • Parameter

    ParameterDescription
    retain_mem
    • TRUE: Retains the available memory in the result cache.

    • FALSE: Releases the available memory in the result cache.

    retain_stat
    • TRUE: Retains statistical information.

    • FALSE: Deletes statistical information.

  • Return Value

    ValueDescription
    BOOLEANReturns TRUE when all objects have been deleted.
  • Example

    BEGIN
        DBMS_RESULT_CACHE.FLUSH;
    END;
    /

28.2.3. INVALIDATE

Invalidates all results dependent on the user specified object.

Details about the INVALIDATE function and procedure are as follows:

  • Prototype

    • object name

      DBMS_RESULT_CACHE.INVALIDATE 
      (
          owner       IN      VARCHAR2,
          name        IN      VARCHAR2
      ) 
      RETURN NUMBER;
      
      DBMS_RESULT_CACHE.INVALIDATE 
      (
          owner       IN      VARCHAR2,
          name        IN      VARCHAR2
      );
    • data dictionary number

      DBMS_RESULT_CACHE.INVALIDATE 
      (
          obj_id      IN      NUMBER
      ) 
      RETURN NUMBER;
      
      DBMS_RESULT_CACHE.INVALIDATE 
      (
          obj_id      IN      NUMBER
      );
  • Parameter

    ParameterDescription
    ownerSchema name.
    nameObject name.
    obj_idData dictionary number.
  • Return Value

    ValueDescription
    NUMBERReturns the number of invalidated objects.
  • Example

    BEGIN
        DBMS_RESULT_CACHE.INVALIDATE('TIBEROPRIGHT', 'EMP');
    END;
    /

28.2.4. INVALIDATE_OBJECT

Invalidates the user-specified results.

Details about the INVALIDATE_OBJECT function and procedure are as follows:

  • Prototype

    • address of a result object

      DBMS_RESULT_CACHE.INVALIDATE_OBJECT 
      (
          id          IN      BINARY_INTEGER
      ) 
      RETURN NUMBER;
      
      DBMS_RESULT_CACHE.INVALIDATE_OBJECT 
      (
          id          IN      BINARY_INTEGER
      );
    • ID number of a result object

      DBMS_RESULT_CACHE.INVALIDATE_OBJECT 
      (
          cache_id    IN      VARCHAR2
      ) 
      RETURN NUMBER;
      
      DBMS_RESULT_CACHE.INVALIDATE_OBJECT 
      (
          cache_id    IN      VARCHAR2
      );
  • Parameter

    ParameterDescription
    idResult object address.
    cache_idResult object ID.
  • Return Value

    ValueDescription
    NUMBERReturns the number of invalidated objects.
  • Example

    BEGIN
        DBMS_RESULT_CACHE.INVALIDATE_OBJECT(3);
    END;
    /

28.2.5. MEMORY_REPORT

Displays the memory usage information of the result cache on the screen. The serveroutput option of tbSQL must be turned on.

Details about the MEMORY_REPORT procedure are as follows:

  • Prototype

    DBMS_RESULT_CACHE.MEMORY_REPORT 
    (
        detailed    IN      BOOLEAN DEFAULT FALSE
    );
  • Parameter

    ParameterDescription
    detailed
    • TRUE: Displays detailed report.

    • FALSE: Displays standard report.

  • Example

    BEGIN
        DBMS_RESULT_CACHE.MEMORY_REPORT;
    END;
    /

28.2.6. STATUS

Returns the current status of the result cache.

Details about the STATUS function are as follows:

  • Prototype

    DBMS_RESULT_CACHE.STATUS
    RETURN VARCHAR2;
  • Return Value

    ValueDescription
    VARCHAR2
    • STATUS_DISA: Result cache cannot be used.

    • STATUS_ENAB: Result cache can be used.

    • STATUS_BYPS: Result cache is temporarily unavailable.

  • Example

    BEGIN
        DBMS_OUTPUT.PUT_LINE(DBMS_RESULT_CACHE.STATUS);
    END;
    /