## 4.4.2.1 Working With The Dynamic Result Cache
This section includes examples of creating the cache and the configuration parameters available to modify the operation of caches and monitoring usage.
## SQL Syntax
The OPTIMIZER ADMIN privilege is required to execute commands to configure the dynamic result cache.
You can add a dynamic cache to a view using either the CREATE VIEW or ALTER VIEW statements. Optionally, a filter condition can be applied to the view. The basic syntax is shown here:
```
CREATE VIEW <view_name> [(column_name_list)] AS <subquery> WITH DYNAMIC CACHE [FILTER <filter_condition>];
```
The option to DROP a cache is also available. Commands for use with ALTER SYSTEM are:
- CLEAR (remove all cache entries)
- REMOVE (remove a single named cache entry)
Refer to the SAP HANA SQL and System Views Reference for full details.
## Examples
The basic usage with ALTER VIEW is shown in this example:
```
ALTER VIEW MyView ADD DYNAMIC CACHE;
```
To create a view with a dynamic result cache the syntax is:
```
CREATE VIEW MyView as (…) WITH DYNAMIC CACHE;
```
The following examples show how the dynamic result cache is used when a filter condition is applied:
1. Y ou can apply a filter using the parameterized filter condition (with a question mark):
```
ALTER VIEW view1 ADD DYNAMIC CACHE FILTER MANDT = ?;
```
Only the equals comparator is permitted but multiple conditions can be used with the AND operator. For example: MANDT = ? AND RLDNR = ?
2. If the cache is defined with a filter then a condition must be applied in the query:
```
SELECT * FROM view1 WHERE MANDT = 300;
```
3. The filter value can also be provided as a runtime parameter using the question mark syntax:
```
SELECT * FROM view1 WHERE MANDT = ?;
```
## Configuration
Dynamic result caches are enabled by default. A number of configuration parameters are available to manage this feature in the 'dynamic_result_cache' section of the ini file:
| Parameter:             | enabled                                                                                                                                                                                                         |
|------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Purpose                | Enable the dynamic cached views feature. If this is set as false then any cached data will be ignored.                                                                                                          |
| Default                | true                                                                                                                                                                                                            |
| Additional information | This functionality is available at run-time using the following two hints: DYNAMIC_RESULT_CACHE - Select data from the dynamic result cache NO_DYNAMIC_RESULT_CACHE - Explicit instruction not to use the cache |
Parameter:
Purpose
Default
Unit
Parameter:
Purpose
Default
Unit
Exceptions total_size
Apply a memory budget, that is, the maximum memory allocation which is available for all dynamic result cache entries.
10000
MB
max_cache_entry_size
Apply a memory budget for individual cache entries, that is, the maximum memory allocation which is available for a single dynamic result cache.
1000
MB
Caches which exceed this size limitation are not used, they are registered on the exclusion list and the fallback query will be used in future. The monitoring view
m_DYNAMIC_RESULT_CACHE_EXCLUSIONS shows a single entry for each cache which has been excluded.
Note that for caches where a filter has been applied exclusion depends on the parameter used and the total size of the result. If the result using one parameter is excluded it may still be possible to use the cache with a different parameter which returns results within the memory budget.
## Parameter:
|                        | You can remove a cache key from the exclusion list by any of the following: 1. Disable dynamic result cache on the view: ALTERVIEW…DROPDYNAMICCACHE; 2. Drop the view: DROPVIEW...; 3. Use the clear command: ALTER SYSTEM CLEAR DYNAMIC RESULT CACHE;                                                                                                                                                                    |
|------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Parameter:             | version_garbage_collection_blocking_period                                                                                                                                                                                                                                                                                                                                                                                |
| Purpose                | This parameter is used to prevent garbage collection from running against the view's base table.                                                                                                                                                                                                                                                                                                                          |
| Default                | 60                                                                                                                                                                                                                                                                                                                                                                                                                        |
| Unit                   | Seconds                                                                                                                                                                                                                                                                                                                                                                                                                   |
| Additional information | A side-effect of the version garbage collection process is that it may invalidate the cache which must then be refreshed. To prevent this from frequently happening, you can use this parameter to control the garbage collection process of the base table to block garbage collection for a limited period of time. Note that if the cache is not used within this period it will be invalidated and must be refreshed. |
| Parameter:             | enable_auto_delta_refresh                                                                                                                                                                                                                                                                                                                                                                                                 |
| Purpose                | Controls automatic delta refresh of dynamic result cache. Use this feature to minimize the possibility of cache invalidation by garbage collection. If this is turned on a background job triggers a refresh of the cache entry's delta whenever it is required. The feature is turned off by default.                                                                                                                    |
| Default                | FALSE                                                                                                                                                                                                                                                                                                                                                                                                                     |
| Parameter:             | enable_implicit_match                                                                                                                                                                                                                                                                                                                                                                                                     |
| Purpose                | Controls implicit matching which automatically attempts to match queries with the dynamic result cache. The feature is enabled by default.                                                                                                                                                                                                                                                                                |
| Default                | TRUE                                                                                                                                                                                                                                                                                                                                                                                                                      |
## Monitoring
Two views are available to analyze the operation of the cache:
m_DYNAMIC_RESULT_CACHE Every cache has its own ID and this view gives full details of each cache including memory size, record count and timestamps for cache and delta store. It also includes the timestamp of the last cached MVCC snapshot which is the basis of the garbage collection process.
This view only shows data for a cache once a query has been executed against the view.
m_DYNAMIC_RESULT_CACHE_EXCLUSIONS Usage of views which cannot be cached is logged and details can be seen in this view. Typically, this is caused by a data selection which exceeds the size defined in
## max_cache_entry_size
max_cache_entry_size (by default 1GB). This view includes memory size, record count and timestamp of the data.
## Implicit Matching
In order to extend the usage of the dynamic result cache to other queries, implicit matching automatically attempts to match queries with cached views.
Using implicit matching the SQL optimizer reads the query plan of the current statement and analyzes the structure to check if the requested data can be retrieved from cached data. If a suitable cache is available and all the checks for data compatibility and user privileges are successful then the SQL optimizer rewrites the execution plan to utilize the existing dynamic result cache.
Implicit matching may incur a slight overhead in query compilation times and although the feature is enabled by default it can be turned off by setting the configuration parameter enable_implicit_match to FALSE .
## Hints for Enabling Implicit Matching
If implicit view matching is disabled it can be used on an ad hoc basis using the following hint:
DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH
Or, conversely, use this hint if necessary to manually turn it off:
NO_DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH
Optionally, implicit matching can be used with a named specific view, or a selection of preferred views:
DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH(&lt;schema_name1>=.&lt;view_name1>=, &lt;schema_name2>=.&lt;view_name2>=, ...)