## 4.4.2.2 Testing the Dynamic Result Cache
You will need to test queries using a dynamic result cache to verify the performance benefit. Some general guidance to support testing is given here.
## Testing and Verification
Important resources for testing include Explain Plan and Plan Visualization, and the monitoring view m_DYNAMIC_RESULT_CACHE. You should be able to step through the complete process of creating and querying the view and measuring the performance. This may include the following actions:
- Building and querying the cache
- Updating the target table and seeing an increase in the delta record count
- Seeing the selection of the appropriate query plan (both reading from the cache and reading from the database) using Plan Visualization.
- Testing the effects of version garbage collection.
- Measuring performance (using hints to turn cache usage on or off).
## Create and Verify the View and Cache
Create the view and user ALTER VIEW to add the dynamic cache. You can then confirm that the cached view was created by checking the system view DYNAMIC_RESULT_CACHE:
```
select schema_name, object_name, cache_type, cache_filter, cache_locations from DYNAMIC_RESULT_CACHE;
```
The expected return values are as follows:
| CACHE_TYPE      | 'DYNAMIC, FULL' (for a dynamic result cache without a filter) or   |
|-----------------|--------------------------------------------------------------------|
|                 | 'DYNAMIC, PARTIAL' (for a cache with a filter).                    |
| CACHE_FILTER    | Filter shown if specified.                                         |
| CACHE_LOCATIONS | Location of cache entry if specified.                              |
## Verify the Query Plan
Run a test query against the cached view. You can now use Explain Plan (either within SAP HANA Studio or from the SQL command line) to verify that the query plan for a dynamic cached view has been created. In the output, search for the operator name property HAVING - DYNAMIC VIEW CACHE. This is only created when the dynamic result cache is active.
For further testing, note that if implicit matching was used using the hint DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH the operator name CONTROL SWITCH will include "AUTO VIEW MATCH" details as one of its properties.
## Build and Monitor the Cache
The cache is built for the first time when you first run the query.
Firstly, you can use the Visualize Plan tool to see which alternative plans are built for the query and which one is executed. Note also the detailed execution times in the tool.
Secondly, you can see full details of the cache refresh process in the monitoring view m_DYNAMIC_RESULT_CACHE. Select all details for the view:
```
select * from m_dynamic_result_cache where object_name = 'MyView';
```
Key values include: Refresh reason (initially 'Cache Creation'), Duration, State, Refresh_Count, Memory_Size, Record_Count.
## Measure the Delta Record Count After Updates
After executing some INSERT operations in the target table you can monitor how the cache is used in this situation.
1. Use Visualize Plan to see which plan is executed. Check the row count numbers to see the effects of adding new data to the table.
2. In the m_DYNAMIC_RESULT_CACHE view, the Delta_Refresh_Count shows an increase in how often delta records have been added to the cache.
## Monitor Version Garbage Collection
Verify the effects of garbage collection by holding queries back for one minute until after the garbage collection process has run.
In the m_DYNAMIC_RESULT_CACHE view, check the value of the last refresh reason time. This will show 'MVCC_GARBAGE_COLLECTION'.
## Test Implicit Matching
To test Implicit Matching you can use the Explain Plan tool in SAP HANA Studio to analyze the details of the execution and optimization processes. You can see which views have been considered by the optimization process by looking at the result value returned by the following procedure:
GET_DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH_CANDIDATES_IN_STATEMENT
The views that passed the checking phase, but were not selected in the rewriting phase of the process will be marked as 'CANDIDATE MATCH'. Explain Plan will show the rewritten plan with the selected view name replacing the original table(s) specified in the statement.