145 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 PL/SQL Language Reference for more information about "Using the Cross-Session PL/SQL Function Result Cache"
-
Oracle Database Performance Tuning Guide for more information about "Result Cache Concepts"
145.1 DBMS_RESULT_CACHE Security Model
Only database administrators should be granted the EXECUTE
privilege for this package.
145.2 DBMS_RESULT_CACHE Constants
The DBMS_RESULT_CACHE
package defines several constants for specifying parameter values.
The following table describes these constants.
Table 145-1 DBMS_RESULT_CACHE Constants
Constant | Definition | Description |
---|---|---|
|
|
Cache has been made temporarilyunavailable. |
|
|
The result cache is in an unusable state. |
|
|
Cache is not available. |
|
|
Cache is available. |
|
|
Cache is available, but synchronizing with Oracle RAC nodes. |
145.3 Summary of DBMS_RESULT_CACHE Subprograms
This table lists the DBMS_RESULT_CACHE
subprograms and briefly describes them.
Table 145-2 DBMS_RESULT_CACHE Package Subprograms
Subprogram | Description |
---|---|
Returns a pipelined BL_TABT. |
|
Adds a |
|
Removes all cache_ids from the black-list. |
|
Removes the cache_id from the black-list. |
|
Sets the bypass mode for the Result Cache |
|
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 |
|
Invalidates all the result-set objects that dependent upon the specified dependency object |
|
Invalidates the specified result-set object(s) |
|
Produces the memory usage report for the Result Cache |
|
Checks the status of the Result Cache |
145.3.1 BLACK_LIST Function
This table function returns all the blocklisted cache ids of a local instance.
Syntax
DBMS_RESULT_CACHE.BLACK_LIST RETURN BL_TABT;
145.3.2 BLACK_LIST_ADD Procedure
This procedure adds a cache_id to the blocklist.
Syntax
DBMS_RESULT_CACHE.BLACK_LIST_ADD ( cache_id IN VARCHAR2, global IN BOOLEAN DEFAULT FALSE);
Parameters
Table 145-3 BLACK_LIST_ADD Procedure Parameters
Parameter | Description |
---|---|
|
The |
|
|
145.3.3 BLACK_LIST_CLEAR Procedure
This procedure removes all cache_ids from the blocklist.
Syntax
DBMS_RESULT_CACHE.BLACK_LIST_CLEAR ( global IN BOOLEAN DEFAULT FALSE);
Parameters
Table 145-4 BLACK_LIST_CLEAR Procedure Parameters
Parameter | Description |
---|---|
|
|
145.3.4 BLACK_LIST_REMOVE Procedure
This procedures removes the cache_id from the blocklist.
Syntax
DBMS_RESULT_CACHE.BLACK_LIST_REMOVE ( cache_id IN VARCHAR2, global IN BOOLEAN DEFAULT FALSE);
Parameters
Table 145-5 BLACK_LIST_REMOVE Procedure Parameters
Parameter | Description |
---|---|
|
The |
|
|
145.3.5 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 145-6 BYPASS Procedure Parameters
Parameter | Description |
---|---|
|
|
|
|
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.
145.3.6 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 145-7 FLUSH Function & Procedure Parameters
Parameter | Description |
---|---|
|
|
|
|
Return Values
TRUE
if successful in removing all the objects.
145.3.7 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 145-8 INVALIDATE Function & Procedure Parameters
Parameter | Description |
---|---|
|
Schema name |
|
Object name |
|
Dictionary object number |
Return Values
The number of objects invalidated.
145.3.8 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 145-9 INVALIDATE_OBJECT Function & Procedure Parameters
Parameter | Description |
---|---|
|
Address of the cache object in the Result Cache |
|
Result cache identifier of a SQL cursor or PL/SQL function. |
Return Values
The number of objects invalidated.
145.3.9 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 145-10 MEMORY_REPORT Procedure Parameters
Parameter | Description |
---|---|
|
|
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;
145.3.10 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.