Databases Reference
In-Depth Information
In step 6 and step 7 we execute the
STRESS
and
STRESS_CACHE
procedures, obtaining
an output as shown in the following screenshot:
In step 8, we clean the database by dropping the procedures and functions created in
this recipe.
We can see a large performance improvement by executing the function defined with
the
RESULT_CACHE
option enabled.
There's more...
Using the result cache can lead to a huge performance gain when we have a deterministic
function—a function which always returns the same result for the same parameters—often
invoked with the same parameters.
The introduction of this change in our PL/SQL code doesn't require a lot of work; it's just
a matter of adding a parameter in the function definition, and there aren't warnings in the
use of this feature.
The result cache can also be used for functions with a result based on the contents of
infrequently updated tables. In this case, when we define a function we add the
RELIES
ON
clause to indicate the table to which the function is related, as in the following example,
where the function
FOO
relies on the table
EMPLOYEES
:
CREATE OR REPLACE FUNCTION FOO (APARAMETER NUMBER, …)
RETURN NUMBER RESULT_CACHE
RELIES ON (EMPLOYEES)
IS…
