Property
|
Description
|
Parameter type
|
String
|
Syntax
|
RESULT_CACHE_MODE = { MANUAL | FORCE }
|
Default value
|
MANUAL
|
Modifiable
|
ALTER SESSION, ALTER SYSTEM
|
Basic
|
No
|
RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan.
Values:
- MANUAL
The ResultCache operator is added only when the query is annotated (that is, hints).
- FORCE
The ResultCache operator is added to the root of all SELECT statements (provided that it is valid to do so).
For the FORCE setting, if the statement contains a NO_RESULT_CACHE hint, then the hint takes precedence over the parameter setting.
Note:
1. The parameter was introduced in 11g. The Query Result Cache feature is available in Oracle Enterprise Edition only. Need to set a minimum value for shared_pool_size to enable result cache.
2. The parameter is related to an 11g new feature: SQL query Result Cache.
A separate shared memory pool is now used for storing and retrieving cached results. Query retrieval from the query result cache is faster than rerunning the query. The new query result cache enables explicit caching of results in database memory. Subsequent queries using the cached results will experience significant performance improvements.
Enable Result Caching: set the RESULT_CACHE_MODE initialization parameter at the system, session, or table level to the following values:
Enable Result Caching: set the RESULT_CACHE_MODE initialization parameter at the system, session, or table level to the following values:
· MANUAL - The results of a query can only be stored in the result cache by using the result_cache hint, such as:
SELECT /*+ result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno;
· FORCE - All results are stored in the result cache. If you do not wish to include the result of the query in the result cache, then you must use the /*+ no_result_cache */ hint in your query:
SELECT /*+ no_result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno;
The database automatically invalidates a cached result whenever a transaction modifies the data or metadata of any of the database objects used to construct that cached result.
There is another related new feature introduced in 11g as PL/SQL Function Result Cache.
There is another related new feature introduced in 11g as PL/SQL Function Result Cache.
3. Query for the current value of the parameter
select name, value, isdefault, isses_modifiable, issys_modifiable,
isinstance_modifiable, isdeprecated, description
from v$parameter
where upper(name) = 'RESULT_CACHE_MODE';
No comments:
Post a Comment