Register Login

SAP HANA View Cache Interview Questions & Answers

Updated May 18, 2018

FAQs: SAP HANA View Cache

1. What does view cache allow?

To cache results on SQL views, calculation views and CDS views the SAP HANA view cache (or result cache extension) is used. This allows quick data retrieval without repeating overhead like search and aggregation.

2. When view cache can be used?

In the following scenario the view cache is particularly helpful:

  • Based on a view complex query
  • Small result is set somewhat
  • In the underlying tables limited amount of changes

3. What benefits does the view cache have?

The following advantages can be provided by the view cache:

Reduction of CPU consumption
Reduction of SAP HANA thread utilization
Performance improvements

4. What are the limitations of view cache?

View cache has the following limitations:

As of SAP HANA SPS 11 it is available

5. In what way the view cache can be administered?

The following commands can be used to administer the view cache:

Command

Details

CREATE FUNCTION ... WITH CACHE RETENTION [<minutes>] ...
CREATE VIEW ... WITH CACHE RETENTION [<minutes>] ...
ALTER VIEW ... ADD CACHE RETENTION [<minutes>] ...
ALTER VIEW ... ALTER CACHE RETENTION [<minutes>] ...

For a specific object these commands allow activating the view cache. <minutes> defines a change that needs to be reflected in the cache at latest after how many minutes. Every change to an underlying table will result in an immediate cache refresh if minutes is set to 0 or not specified.

Additional options like a projection list or filter conditions can be used.

ALTER VIEW ... DROP CACHE

An existing view cache is dropped.

ALTER SYSTEM CLEAR RESULT CACHE

All view cache entries are removed globally.

ALTER SYSTEM REFRESH RESULT CACHE <object_name>

For the specified object the view cache is refreshed.

ALTER SYSTEM REFRESH RESULT CACHE ENTRY <cache_id>

With the specified <cache_id> the view cache entry is refreshed.

ALTER SYSTEM REMOVE RESULT CACHE ENTRY <cache_id>

With the specified <cache_id> the view cache entry is removed. The cache entry is rebuilt with the next access.

6. In what way the view cache can be used by a query?

The utilization of the view cache can be controlled by the following hints:

Hint

Details

NO_RESULT_CACHE

Bypass view cache (default)

RESULT_CACHE

Force utilization of view cache if available

Further special hints like RESULT_CACHE_MAX_LAG, RESULT_CACHE_NON_TRANSACTIONAL, RESULT_CACHE_NO_REFRESH, RESULT_LAG, RESULT_CACHE_AFTER_ANALYTIC_PRIVILEGE and RESULT_CACHE_BEFORE_ANALYTIC_PRIVILEGE are described in the SAP HANA documentation.

7. In what way the view cache can be monitored?

View cache related information by the following monitoring views:

Monitoring view

Details

M_RESULT_CACHE_EXCLUSIONS

Exclude view cache.

In this list the views won't be cached. By SAP HANA they are automatically maintained, e.g. in case of large result sets.

M_RESULT_CACHE
RESULT_CACHE

View cache General information

RESULT_CACHE_COLUMNS

In view cache information about columns considered

 

SQL statement

Details

SQL: "HANA_Memory_ViewCache"

General view cache information

View cache specific information is provided by the columns HASH_CACHE, CACHE_RETENTION, CACHE_FILTER and IS_CACHE_FORCED in view VIEWS.

8. For storing view cache information which memory area is used?

In the following heap allocator view cache information is stored:

Pool/RowEngine/ViewCache

9. How the query result cache can be differentiated?

Since SAP HANA SPS 06 the query result cache is available and also caches view results. Listed below are the restrictions that apply:

  • If all underlying tables are located in column store then only it is valid
  • When one of the underlying table is updated cache is generally updated
  • A separate cache entry is there for each query and parameter set

In general the use of view cache is recommended rather than the query result cache.


×