Caching is used widely in SAP HANA as a strategy to improve performance by re-using queried data rather than re-reading and processing the data every time it is requested. The static result cache and the dynamic result cache are applications of this.
The static result cache is created for a specific view and remains valid for the duration of a user-defined retention period. The dynamic result cache is similar but does not have a retention period; it guarantees transnational consistency by maintaining delta records of all changes applied to the underlying table.
Static Cache vs Dynamic Cache
Static Result Cache
Query result on complex view is cached and refreshed periodically. The maximum staleness restriction can be specified by users.
Ease of use: No application modification necessary
Selectively enabled for sets of queries using a view definition (DDL) statement
Two types of view caches:
– aggregated cache that allows explicit aggregation types for caching (MIN, MAX, COUNT, SUM)
– parameterized cache (caching of table function)
indexserver.ini -> [result_cache] -> enabled = yes (default: no);
indexserver.ini -> [result_cache] -> total_size = <MB> (default: 10000);
How to use
View related DDL:
CREATE VIEW SIMPLE_VIEW AS (…) WITH CACHE RETENTION 100;
ALTER VIEW <view_name> DROP | ADD | ALTER CACHE …;
Table function related DDL:
CREATE FUNCTION SIMPLE_FUNCTION WITH CACHE RETENTION 100;
SELECT * FROM M_RESULT_CACHE; shows cache-related information, e.g. cache id, cache key, memory size, record count
SELECT * FROM M_RESULT_CACHE_EXCLUSIONS; list of excluded views from caching
ALTER SYSTEM CLEAR RESULT CACHE;
ALTER SYSTEM REFRESH RESULT CACHE <object_name>;
In order to demonstrate on static cache, I have created a simple calculation view CA_PFC_COMBINED_FACT_RC and it uses a column table PFC_COMBINED_FACTS as source table. this view returns a bunch of measures grouped by PFCDIK.
Let’s do a data preview for this view. it takes 3.5 seconds to execute the query.
and if you highlight the SQL statement generated and click with ‘Explain Plan’
It shows that the view result is aggregated from column table PFC_COMBINED_FACTS directly
Let’s run the ALTER VIEW statement to enable static cache for this view and for retention we assign 100 minutes
The following system views can help us to find out a lot of information about cached views, columns and properties.
The system view VIEWS shows this calculation view has been enabled for static full cache
The system view RESULT_CACHE lists all cached views
The system view RESULT_CACHE_COLUMNS lists all cached columns
Let’s add static cache for this view so we can see the difference for performance.
But before start we need to check monitor view M_RESULT_CACHE to see if cache has been added
till now cache has been enabled but not added yet. We need to run the SQL statement to populate the result cache
The first run seems pretty normal and performance also looks like same but let’s check cache monitoring view again
WOW static cache has been populated already do we expect performance improvement at next run?
Let’s execute the SQL statement again and this time it takes only 22 ms (0.022 sec) instead of 3.2 sec to execute the query.
The ACCESS_COUNT column specifies the number of accesses on the cache entry. Every access on the cache increases hit 1 until cache retention exceed
You can refresh cache by running the ALTER SYSTEM statement
and the REFRESH_COUNT will be increased after cache refresh.
Let’s take a look at the Explain Plan and Execution Plan. The Explain Plan shows the view result is from result cache, not from column table
Execution plan shows the view result is from result cache as well
Of course, you can wipe out the result cache by running the following ALTER SYSTEM statement
Or just disable result cache for this view by running the ALTER VIEW statement
After cache is dropped for this view let’s check system view again
Also flag HAS_CACHE has been changed from ‘STATIC,FULL’ to ‘NONE’
Dynamic Result Cache
The dynamic result cache may be used to improve the performance of queries which are frequently executed but most importantly it eliminates the risk of querying stale data and will always return transitionally consistent data.
Intensive parallel querying of large tables
Extensive use of aggregation
Tables are regularly updated and up-to-date query results are essential.
indexserver.ini -> [dynamic_result_cache] -> enabled = yes (default: no);
indexserver.ini -> [dynamic_result_cache] -> max_cache_entry_size = <MB> (default: 1000);
indexserver.ini -> [dynamic_result_cache] -> total_size = <MB> (default: 10000);
ndexserver.ini -> [dynamic_result_cache] -> version_garbage_collection_blocking_period= 60;
How to use
SQL view only:
CREATE VIEW SIMPLE_VIEW AS (…) WITH DYNAMIC CACHE;
ALTER VIEW <view_name> DROP | ADD CACHE …;
SELECT * FROM DYNAMIC_RESULT_CACHE;
SELECT * FROM RESULT_CACHE_COLUMNS;
SELECT * FROM M_DYNAMIC_RESULT_CACHE;
SELECT * FROM M_DYNAMIC_RESULT_CACHE_EXCLUSIONS;
ALTER VIEW … DROP DYNAMIC CACHE;
ALTER SYSTEM CLEAR DYNAMIC RESULT CACHE; –remove all cache entries
ALTER SYSTEM REMOVE DYNAMIC RESULT CACHE; –remove a single named cache entry
Dynamic Cache Example
Let’s create a SQL view with dynamic cache enabled
If you want to enable existing SQL views you can simply run ALTER VIEW ADD DYNAMIC CACHE
The following system views can help us to find out how many views and columns have been enabled for dynamic cache in the system.
This SQL view has been enabled for dynamic full cache
The system view DYNAMIC_RESULT_CACHE lists all enabled dynamic cache views
The system view RESULT_CACHE_COLUMNS lists all enabled dynamic cache columns
Before we execute the sql statement to add dynamic cache let’s check system view M_DYNAMIC_RESULT_CACHE to see if dynamic cache exists
Ok it is clear the dynamic cache has been enabled but not added yet. We can execute the SQL statement to populate the dynamic cache
Right after we execute and let’s check the monitoring view again to see if there is any change. Ok the dynamic cache has been added.
Let’s rerun the SQL statement and this time it takes only 25 ms instead of 3.6 seconds to execute the query. dynamic cache takes effect
Let’s take a look at both Explain Plan and Execution Plan
The Explain Plan shows the view result is from dynamic result cache, not from column table
Execution plan shows the view result is from dynamic result cache as well
What about executing some INSERT operations in the column table and can you expect fresh (not stale) data with the same performance?
After inserting new row into column table let’s run the SQL statement and this time it takes only 34 ms to execute the query. it take bit longer but data is up to date. We can see the new row returning from query with good performance. It looks like the cache has been updated dynamically.
Let’s remove it from column table by executing one DELETE operations
PFCDIK 1000 has been deleted from target table and dynamic cache get updated again with disappearing of that row and query speed is still fast.
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.
Since dynamic cache is limited to SQL view only and you can work around this by wrapping SQL view inside the calculation view as data source
Now let’s have data preview for this CV
Check M_DYNAMIC_RESULT_CACHE system view and you can find out that dynamic cache has been added for data source (SQL view)
From execution plan of calculation view you can see it bypass the column table search and view result is from dynamic cache.