Database Reference
In-Depth Information
When to Use It
If you're dealing with a performance problem caused by an application that executes the same operation over and
over again, you have to reduce either the frequency of execution or the response time of the operation. Ideally,
you should do both. However, sometimes (for example, when the application's code can't be modified) you can
implement only the latter. To reduce response time, you should initially employ the techniques presented in
Chapters 13 and 14. If this isn't enough, only then should advanced optimization techniques, such as result caches,
be considered. Basically, result caches are effective given two conditions. First, the same data is queried more often
than it's modified. Second, there is enough memory to cache the result sets.
In most situations, you shouldn't enable the result cache for all queries. In fact, most of the time, only specific
queries can benefit from the result cache. For other queries than those specific ones, result cache management is
simply pure overhead that might also overstress the cache. Also keep in mind that server-side caches are shared
by all sessions, so their access is synchronized (they can become a point of serialization like any shared resource).
Therefore, you should enable result caches only for the queries, subqueries, and tables requiring them. In other
words, the result cache should be enabled selectively and only when it's really necessary to improve performance.
The server result cache doesn't completely avoid the overhead of executing a query. This means that if a query
already performs relatively few logical reads (and no physical reads) without using the result cache, it won't be much
faster when using it. Remember, both the buffer cache and the result cache are stored in the same shared memory.
The PL/SQL function result cache is especially useful for functions that are frequently called from SQL
statements. In fact, it isn't uncommon for such functions to be called for every row that is either processed or
returned, whereas the input parameters are different on only a few rows. However, functions that are frequently called
from PL/SQL can also take advantage of the result cache.
Because of the problem with consistency, the client result cache should be used only for read-only or
read-mostly tables.
Finally, note that you can take advantage of server and client result caches at the same time. However, for the
queries executed by the client, you can't choose to bypass the client result cache and use the server result cache only.
In other words, both result caches are used.
Pitfalls and Fallacies
As pointed out in the previous sections, the consistency of the results isn't guaranteed in the following cases:
result_cache_remote_expiration
initialization parameter is set to a value greater
than 0 and queries via database link are executed
•
When the
RELIES_ON
•
When, in version 11.1, PL/SQL functions that don't specify (or wrongly specify) the
clause are defined
•
When the client result cache is used
In such cases, therefore, it's best to avoid result caches, unless you fully understand and accept the implications
of each of these situations.
Caching the result of queries that reference nondeterministic PL/SQL functions, or functions that are sensitive to
session-specific settings like NLS parameters and contexts, might not work as you expect. The issue is that by default,
the database engine considers those functions as deterministic, and consequently, wrong results might be generated.
Several examples are provided in the
rc_query_nondet.sql
script. The following example is one of them (notice how
the second query returns a wrong result):
SQL> CREATE OR REPLACE FUNCTION f RETURN VARCHAR2
2 IS
3 l_ret VARCHAR2(64);
4 BEGIN
