目录

SAP-HANA-Scale-out-04缓存

JAY.LIN 收录于未分类

2025-09-16 约 1049 字预计阅读 3 分钟

https://bing.ee123.net/img/rand?artid=151757176

目录

SAP HANA Scale-out 04：缓存

结果缓存

静态结果缓存 Vs 动态结果缓存

Feature	Static Result Cache	Dynamic Result Cache
Target Scenario	对复杂视图（通常是顶层视图）的查询	频繁更新的大表（例如ACDOCA）上的聚合查询
Query result	非实时数据	实时数据
Scope	Target objects: SQL View, User-defined table function, Calculation view Aggregation types: SUM, MIN, MAX, COUNT.	Target objects: SQL Views on the aggregation of a single column table. Aggregation types: SUM, COUNT, AVG -完全支持 MIN, MAX- 部分支持
Cache Maintenance	设定失效时间，到期后重新全量刷新	每次查询执行时：如果能够识别更新的记录，则缓存会增量更新这些记录。如果无法识别更新（由于多版本并发控制垃圾回收）则缓存将完全刷新。
Implicit view matching	不支持	Supported with hint / configuration.
Adoption effort: defining cache	通常基于CDS view或calculation view定义static result cache In the case of a CDS view without aggregation the result cache should be defined with expected aggregation type from target queries.	If the target aggregation is already defined as a view, dynamic result can be enabled on the existing view (explicit usage). Otherwise, a new view definition is required and dynamic result cache can be used with implicit view matching.
Adoption effort: enable cache utilization	使用hint访问缓存数据	Dynamic result cache is utilized by default even without a hint (but hint or configuration is required for enabling implicit view matching in the current release.)

Static Result Cache

语法


--创建视图时指定缓存配置
CREATE VIEW ... WITH CACHE RETENTION [<minutes>] ...
--修改视图-调整缓存配置
ALTER VIEW ... ADD CACHE RETENTION [<minutes>] ...
--修改视图-增加缓存配置
ALTER VIEW ... ALTER CACHE RETENTION [<minutes>] ...
--删除缓存配置
ALTER VIEW ... DROP CACHE

--刷新缓存数据
ALTER SYSTEM REFRESH RESULT CACHE <object_name>
ALTER SYSTEM REFRESH RESULT CACHE ENTRY <cache_id>
--清空缓存数据，下次访问时重建
ALTER SYSTEM REMOVE RESULT CACHE ENTRY <cache_id>

缓存示例


--创建视图
CREATE VIEW ZSV_ZAD141
AS 
(SELECT * FROM "_SYS_BIC"."CNXXXXX141" )

--配置视图缓存
ALTER VIEW ZSV_ZAD141
ADD CACHE RETENTION 60;

--配置视图缓存（指定字段）
ALTER VIEW ZSV_ZAD141
ADD CACHE RETENTION 120 
OF A, SUM(KF1), MIN(KF2), MAX(KF3); 

--配置视图缓存（过滤器）
ALTER VIEW ZSV_ZAD141
ADD CACHE RETENTION 120 
FILTER B > 3;

使用缓存


--查询时使用缓存
SELECT * FROM ZSV_ZAD141 WITH HINT(RESULT_CACHE);
--查询时跳过缓存
SELECT * FROM ZSV_ZAD141 WITH HINT(NO_RESULT_CACHE);

--通过Explain Plan 检查查询是否使用了缓存

缓存监控


--M_HEAP_MEMORY
SELECT * FROM M_HEAP_MEMORY;
--静态缓存信息
SELECT * from RESULT_CACHE
WHERE 1 = 1
	AND OBJECT_NAME = 'SIMPLE_VIEW';
--静态缓存字段信息
ELECT * FROM RESULT_CACHE_COLUMNS
--检查缓存刷新及访问信息
SELECT * FROM M_RESULT_CACHE;

Dynamic Result Cache

语法


--启用动态缓存
CREATE VIEW ... WITH DYNAMIC CACHE ...
ALTER VIEW ... ADD DYNAMIC CACHE ...
ALTER VIEW ... ALTER DYNAMIC CACHE ...
--禁用动态缓存 
ALTER VIEW ... DROP DYNAMIC CACHE
--清除所有动态缓存
ALTER SYSTEM CLEAR DYNAMIC RESULT CACHE
--清除指定动态缓存
ALTER SYSTEM REMOVE DYNAMIC RESULT CACHE ENTRY <cache_id>

缓存配置


--创建视图
CREATE VIEW ZSV_ZAD142
AS 
(
	SELECT FKDAT_H_YM,VKORG,SUM(KWMENG) AS KWMENG
	FROM "SAPHANADB"."/BIC/AZOSD0022" 
	GROUP BY FKDAT_H_YM,VKORG
);

--配置视图缓存
ALTER VIEW ZSV_ZAD142
ADD DYNAMIC CACHE;

--配置视图缓存（过滤器）
ALTER VIEW ZSV_ZAD142
ADD DYNAMIC CACHE
FILTER  B > 3;

使用缓存


--不使用动态缓存
SELECT VKORG,KWMENG FROM ZSV_ZAD142 WITH HINT(NO_DYNAMIC_RESULT_CACHE)
--默认会使用动态缓存
SELECT VKORG,KWMENG FROM ZSV_ZAD142 WITH HINT(DYNAMIC_RESULT_CACHE)

--启用匹配：查询底表时自动查找是否有匹配的动态缓存
SELECT VKORG,sum(KWMENG) FROM "SAPHANADB"."/BIC/AZOSD0022"
group by VKORG
WITH HINT(DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH)
--禁用匹配:查询底表时禁止自动查找动态缓存
SELECT VKORG,sum(KWMENG) FROM "SAPHANADB"."/BIC/AZOSD0022"
group by VKORG
HINT(NO_DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH)

--使用GET_DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH_CANDIDATES_IN_STATEMENT检查匹配

缓存监控

SELECT * FROM M_DYNAMIC_RESULT_CACHE;