Database Reference
In-Depth Information
In the following example, the function is called 10,000 times without taking advantage of the result cache
(with the procedure
bypass
, the cache is temporarily disabled). The execution takes about 4 seconds:
SQL> execute dbms_result_cache.bypass(
bypass_mode
=>
TRUE
, session => TRUE)
SQL> SELECT count(f(1)) FROM t;
COUNT(F(1))
-----------
10000
Elapsed: 00:00:
04.02
Now, let's call the function 10,000 times again, but this time with the result cache enabled. The execution takes
only about three hundredths of a second:
SQL> execute dbms_result_cache.bypass(
bypass_mode
=>
FALSE
, session => TRUE)
SQL> SELECT count(f(1)) FROM t;
COUNT(F(1))
-----------
10000
Elapsed: 00:00:
00.03
From version 11.2 onward, the database engine automatically detects which tables a function relies on. Based on
that information, the result cache entries can be automatically invalidated whenever a transaction modifies any rows
of a table a result cache entry relies on.
■
In version 11.1, the result cache entries are invalidated only if the
RELIES_ON
clause is specified at the
function level. The purpose of the
RELIES_ON
clause is to specify which tables the return value of the function depend on.
This information is critical to the invalidation of the cache entries. If it's not specified, or contains wrong information, no
invalidation will occur because of modifications that take place in the objects on which the function depends.
Consequently, stale results can occur.
Caution
There are a few limitations to the utilization of the PL/SQL function result cache. The result cache can't be used
for the following functions:
OUT
and/or
IN OUT
parameters
•
Functions with
•
Functions that are defined with invoker's rights (this limitation no longer exists from version
12.1 onward)
•
Pipelined table functions
•
Functions defined in anonymous blocks
IN
parameters or return values of the following types: LOBs,
REF CURSOR
,
•
Functions with
