SAP HANA Result Cache: Static and Dynamic

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

Basic Concept

Query result on complex view is cached and refreshed periodically. The maximum staleness restriction can be specified by users.

Features

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)

Configuration:

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;

Monitoring Views:

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

System Commands:

ALTER SYSTEM CLEAR RESULT CACHE;
ALTER SYSTEM REFRESH RESULT CACHE <object_name>;

Example

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
ALTER VIEW "_SYS_BIC"."Developer_Test.DLIU/CA_PFC_COMBINED_FACT_RC" ADD

CACHE RETENTION 100

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_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

and the REFRESH_COUNT will be increased after cache refresh.

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

ALTER SYSTEM CLEAR RESULT CACHE

Or just disable result cache for this view by running the ALTER VIEW statement

ALTER VIEW "_SYS_BIC"."Developer_Test.DLIU/CA_PFC_COMBINED_FACT_RC" DROP CACHE

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

Basic Concept

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.

Optimal scenarios

Intensive parallel querying of large tables
Extensive use of aggregation
Tables are regularly updated and up-to-date query results are essential.
Configuration
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 …;

System Views:

SELECT * FROM DYNAMIC_RESULT_CACHE;
SELECT * FROM RESULT_CACHE_COLUMNS;

Monitor Views:

SELECT * FROM M_DYNAMIC_RESULT_CACHE;
SELECT * FROM M_DYNAMIC_RESULT_CACHE_EXCLUSIONS;

System Commands:

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

CREATE VIEW "DLIU"."MYDYNAMICCACHEVIEW" as (

SELECT

"PFCDIK",

sum("FRMLNUM1") AS "FRMLNUM1",

sum("FRMLNUM2") AS "FRMLNUM2",

sum("FRMLNUM3") AS "FRMLNUM3",

sum("FRMLNUM4") AS "FRMLNUM4",

sum("FRMLNUM5") AS "FRMLNUM5",

sum("FRMLNUM6") AS "FRMLNUM6",

sum("FRMLNUM7") AS "FRMLNUM7",

sum("FRMLNUM8") AS "FRMLNUM8",

sum("FRMLNUM30") AS "FRMLNUM30",

sum("FRMLNUM31") AS "FRMLNUM31",

sum("FRMLNUM34") AS "FRMLNUM34",

sum("FRMLNUM39") AS "FRMLNUM39",

sum("BALNOMVAL") AS "BALNOMVAL"

FROM "DLIU"."PFC_COMBINED_FACTS"

GROUP BY "PFCDIK"

) WITH DYNAMIC CACHE;

If you want to enable existing SQL views you can simply run ALTER VIEW ADD DYNAMIC CACHE

ALTER VIEW "DLIU"."MYDYNAMICCACHEVIEW" 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.

-- to check view metadata

SELECT schema_name, view_name, has_cache

FROM views

WHERE view_name = 'MYDYNAMICCACHEVIEW';

SELECT * FROM DYNAMIC_RESULT_CACHE;

SELECT * FROM RESULT_CACHE_COLUMNS;

This SQL view has been enabled for dynamic full cache

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

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?

INSERT INTO "DLIU"."PFC_COMBINED_FACTS"

(

"PFCDIK",

"PFCHOLIK", --it is mandatory field but not required for view

"FRMLNUM1",

"FRMLNUM2",

"FRMLNUM3",

"FRMLNUM4",

"FRMLNUM5",

"FRMLNUM6",

"FRMLNUM7",

"FRMLNUM8",

"FRMLNUM30",

"FRMLNUM31",

"FRMLNUM34",

"FRMLNUM39",

"BALNOMVAL"

)values (

1000, -- "PFCDIK"

1000,

800.00,

800.00

)

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.