Databases Reference
In-Depth Information
There are also two initialization parameters to take care of:
RESULT_CACHE_MAX_SIZE
, set to zero to disable the server-side
cache and
CLIENT_RESULT_CACHE_SIZE
, set to a value greater
than or equal to 32K to enable the client-side cache.
In the above statements, we have used the
FORCE
mode, which always enables the client-side
result cache, except when the
no_result_cache
hint is specified in the query.
Please note that table annotation takes precedence over session
or system-wide settings.
The client-side result cache enables applications that use the Oracle Call Interface (OCI) to
connect to the database to cache result sets on the client side. Memory will probably be less
expensive on the client side than on the server side and it assures more locality by reusing
the same query. This is more likely to happen on the same client with the same application.
So caching on the client side results in a higher probability to find the data we need already
in memory than when caching on the server side. Round-trips to the server are also avoided
when the query can be answered using the client result cache.
Client-side result cache is always kept consistent with server data and metadata, eliminating
the need for each application that uses OCI to implement its own custom caching mechanism.
The client-side result cache is different from the server result
cache. It's available from version 11.1 upward of OCI client
libraries, connected to an Oracle database 11
g
R1 or above.
Configuring the client-side result cache
An important parameter in setting the client-side result cache is
CLIENT_RESULT_CACHE_LAG
.
This parameter allow us to define the number of milliseconds elapsed before flushing the
cache. For example, if we set this parameter to 10000, as follows:
ALTER SYSTEM SET CLIENT_RESULT_CACHE_LAG=10000 SCOPE=SPFILE;
The client result cache can lag 10 seconds behind any changes in the database that affect
its result sets.
Due to its static nature, this parameter needs to be set in the
SPFILE
and will be effective
after the instance restarts.
See also
F
The
Taking
advantage
of
function
result
cache
recipe in
Chapter
6
,
Optimizing PL/
SQL Code
, to see the result cache applied in enhancing functions performance
