EDUCAÇÃO E TECNOLOGIA

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

Static Result 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

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

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 "SCD"."PFC_COMBINED_FACTS"

GROUP BY "PFCDIK"

Statement 'SELECT "PFCDIK", sum("FRMLNUM1") AS "FRMLNUM1", sum("FRMLNUM2") AS "FRMLNUM2", sum("FRMLNUM3") AS ...'
successfully executed in 3.312 seconds (server processing time3.294 seconds)

Fetched 1000 row(s) in 75 ms 32 µs (server processing time: 2 ms 215 µs)

Result limited to 1000 row(s) due to value configured in the Preferences

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

ALTER SYSTEM CLEAR RESULT CACHE

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

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.

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

SELECT FROM "DLIU"."MYDYNAMICCACHEVIEW"

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"

     "FRMLNUM1",

     "FRMLNUM2",

     "FRMLNUM3",

     "FRMLNUM4",

     "FRMLNUM5",

     "FRMLNUM6",

     "FRMLNUM7",

     "FRMLNUM8",

     "FRMLNUM30",

     "FRMLNUM31",

     "FRMLNUM34",

     "FRMLNUM39",

     "BALNOMVAL"

)values (

     1000, 

     1000,

     )

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

DELETE FROM "DLIU"."PFC_COMBINED_FACTS"

WHERE "PFCDIK" = 1000

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.