Database Reference
In-Depth Information
There are a few, albeit obvious, limitations with the utilization of the result cache:
•
Queries that reference nondeterministic SQL functions, sequences, and temporary tables
aren't cached.
•
Queries that violate read consistency aren't cached. For example, the result set created by a
session with outstanding transactions on the referenced tables can't be cached.
•
Queries that reference data dictionary views aren't cached.
DBMS_reSULt_CaChe
You can use the
dbms_result_cache
package to manage the result cache. To do this, it provides the following
subprograms:
•
bypass
temporarily disables (or enables) the result cache at the session or system level.
•
flush
removes all the objects from the result cache.
•
invalidate
invalidates all result sets that are dependent on a given database object.
•
invalidate_object
invalidates a single cache entry.
•
memory_report
produces a report on memory utilization.
•
status
shows the status of the result cache.
PL/SQL Function Result Cache
The PL/SQL function result cache is similar to the server result cache, but it supports PL/SQL functions. It also shares
the same memory structures as the server result cache. Its purpose is to store the return value of PL/SQL functions
(and only the return value of functions—the result cache can't be used for
OUT
parameters) in the result cache.
Obviously, functions with different input values are cached in separate cache entries. The following example, an
excerpt of the output generated by the
rc_plsql.sql
script, shows a function for which the result cache is enabled.
To enable it, the
RESULT_CACHE
clause is specified:
SQL> CREATE OR REPLACE FUNCTION f(p IN NUMBER)
2 RETURN NUMBER
3
RESULT_CACHE
4 IS
5 l_ret NUMBER;
6 BEGIN
7 SELECT count(*) INTO l_ret
8 FROM t
9 WHERE id = p;
10 RETURN l_ret;
11 END;
12 /
