Database Reference
In-Depth Information
The following are the dynamic initialization parameters that control the server result cache:
•
result_cache_mode
specifies in which situation the result cache is used. You can set it either
to
manual
, which is the default, or to
force
. With
manual
, the result cache is used only if it's
enabled either through the
result_cache
hint or the
result_cache
clause. With
force
, the
result cache is used for all queries unless the
no_result_cache
hint is specified. Because in
most situations you want to use the result cache for a limited number of queries, I advise you
to leave this initialization parameter to its default value.
•
result_cache_max_size
specifies (in bytes) the amount of shared pool memory that can be
used for the result cache. If it's set to 0, the feature is completely disabled. The default value,
which is greater than 0, is derived from the size of the shared pool. Memory allocation is
dynamic, and therefore, this initialization parameter specifies only the upper limit. You can
display the currently allocated memory by using a query like the following:
SQL> SELECT name, sum(bytes)
2 FROM v$sgastat
3 WHERE name LIKE 'Result Cache%'
4 GROUP BY rollup(name);
NAME SUM(BYTES)
------------------------ ----------
Result Cache 145928
Result Cache: Bloom Fltr 2048
Result Cache: Cache Mgr 208
Result Cache: Memory Mgr 200
Result Cache: State Objs 2896
151280
result_cache_max_result
specifies (in percent) the amount of
result_cache_max_size
that
any single entry in the result cache can use. The default value is 5. Values from 0 to 100 are
allowed.
•
result_cache_remote_expiration
specifies (in minutes) the temporal validity of an entry
in the result cache based on remote objects. This is necessary because the invalidation of
entries based on remote objects isn't performed when the remote objects have been changed.
Instead, the entries are invalidated when the temporal validity defined by this initialization
parameter is elapsed. The default value is 0, which means that the caching of queries based on
remote objects is disabled.
The
result_cache_max_size
and
result_cache_max_result
initialization parameters can be changed at the
system level only. In addition, in a 12.1 multitenant environment, they can only be set at the CDB level. The other two,
result_cache_mode
and
result_cache_remote_expiration
, can also be changed at the session level.
•
■
Setting the
result_cache_remote_expiration
initialization parameter to a value greater than 0 can lead
to stale results. Therefore, you should use values greater than 0 only if you fully understand and accept the implications
of doing so.
Caution
