Symptom
This Knowledge Base Article (KBA) is part of a series of HANA Operation Recommendations.
Its focus is on providing best practice instruction on handling HANA Alerts related to SQL Plan Cache (Alert ID 58 - Plan Cache Size).
End-Users may experience performance issues due to:
Its focus is on providing best practice instruction on handling HANA Alerts related to SQL Plan Cache (Alert ID 58 - Plan Cache Size).
End-Users may experience performance issues due to:
- Frequent Eviction of SQL Execution Plans from Cache and
- SQL Statement Recompilation
Environment
SAP HANA Database SPS 07 and higher.
Instructions in this Knowledge Base Article have been created on a SAP HANA Database Revision 80 (Support Package Stack SPS08) and may differ in other revisions.
Instructions in this Knowledge Base Article have been created on a SAP HANA Database Revision 80 (Support Package Stack SPS08) and may differ in other revisions.
Cause
In heavy loaded systems with a large variety of different or complex
statements or systems making extensive use of native data models (e.g.
from HANA Live), it may happen that the plan cache is too small to
accommodate hot working set of the system leading to frequent eviction
of execution plans from cache and statement recompilation. Consequently,
performance will be suboptimal.
Resolution
PROCEDURE
- Check Alerts
- Check Plan Cache Overview
- Calculate recommended size of Plan Cache of indexserver
- Change Configuration Parameter
- Test and validate new Configuration
- Further recommendations if countermeasures do not not help
PATH
- SAP HANA Studio - SAP HANA Administration Console - Alerts
- SAP HANA Studio - SAP HANA Administration Console - Performance - Load
- SAP HANA Studio - SAP HANA Administration Console - SQL Console
- SAP HANA Studio - SAP HANA Administration Console - Configuration
HOW-TO
1. Check Alerts
Access Alert Tab in HANA Studio and check 'Current Alerts':
a.) What is the priority of the alert?
b.) Is a service other than indexserver affected?
c.) Is an issue occurring on one host only or on multiple hosts?
Check all alerts and put a filter on Alert ID 58 and select the last 3 weeks:
d.) Is it a one-time or temporary issue or are there frequent occurrences based on historic alert data?
2. Check Plan Cache Overview
Plan Cache Statistics are stored in M_SQL_PLAN_CACHE_OVERVIEW. Access the SQL Editor of HANA Studio and use the following query that is based on the recommendations from SAP Note 1969700. The SQL query used is HANA_SQL_SQLCache_Overview_Rev70+ with 2 changes:- Its output is assimilated to the Column names of M_SQL_PLAN_CACHE_OVERVIEW.
- In the modification section the output is limited to the indexserver. In case you need to analyze alerts relating to services other than the indexserver, then change the port from '%03' to '%'.
SELECTSO.HOST,
LPAD(SO.PORT, 5) PORT, LPAD(TO_DECIMAL(SO.PLAN_CACHE_CAPACITY / 1024 / 1024 / 1024, 10, 2), 7) PLAN_CACHE_CAPACITY_GB, LPAD(TO_DECIMAL(SO.CACHED_PLAN_SIZE / 1024 / 1024 / 1024, 10, 2), 7) CACHED_PLAN_SIZE_GB, LPAD(SO.PLAN_CACHE_HIT_RATIO, 4) PLAN_CACHE_HIT_RATIO, LPAD(ROUND(SO.EVICTED_PLAN_COUNT / SECONDS_BETWEEN(H.VALUE, CURRENT_TIMESTAMP) * 3600), 11) EVICTED_PLAN_COUNT_PER_H FROM ( SELECT /* Modification section */ '%' HOST, '%03' PORT /* Replace ‘%03’ with ‘%’ if alert is raised for other than indexserver */ FROM DUMMY ) BI, M_SQL_PLAN_CACHE_OVERVIEW SO, M_HOST_INFORMATION H WHERESO.HOST LIKE BI.HOST ANDTO_CHAR(SO.PORT) LIKE BI.PORT ANDH.HOST = SO.HOST ANDH.KEY = 'start_time' |
3. Calculate recommended size of Plan Cache of indexserver
Based on the type of applications running on SAP HANA, the recommended Plan Cache Size is calculated differently. Please refer to SAP Note 2040002 for latest recommendations (This KBA refers to version 2).Note that all input parameters used in this calculation imply an equilibrated system. This state is typically reached a couple of days after system start-up and can be validated by applying daily checks on M_PLAN_CACHE_OVERVIEW and reviewing the LOAD Monitor:
a.) Non ABAP System:
Recommended PLAN_CACHE_HIT_RATIO > 85. Calculate the recommended Plan Cache Size with the following formula:
PLAN_CACHE_SIZE > 85 * (current PLAN_CACHE_CAPACITY)/(current PLAN_CACHE_HIT_RATIO)
b.) ABAP System:
In ABAP System, the App server caches
lots of statements so you need to have large SQL Plan Cache. Calculate
the recommended Plan Cache Size with the following formula:
PLAN_CACHE_SIZE = # of SQL Connections * 15 MB
Example: If your system has # of SQL connections: 1228 then your plan cache size should be bigger than 1228 * 15 MB <= 20 GB.
But this is just a guideline. You have to check the PLAN_CACHE_HIT_RATIO to find the best plan cache capacity for you.
|
c.) Mixed System:
See b.)
4. Change Configuration Parameter
You need to increase your system's plan cache capacity when either one of the following criteria applies (refer to SAP Note 2040002 for latest updates):- Alert 58 - 'Plan Cache Size' has been raised with Priority 'high'
- CACHED_PLAN_SIZE is bigger than PLAN_CACHE_CAPACITY
- PLAN_CACHE_HIT_RATIO is lower than 90%
- Number of Plan Cache Evictions per hour increases rapidly
indexserver.ini, section [sql], plan_cache_size. Double and defines the size of the SQL plan cache in bytes. Parameter Changes need to be done utilizing HANA Studio UI.Distributed Systems: In distributed systems, every node has its own SQL plan cache. For most cases it is recommended to set Plan Cache Size on system level using the above formulas with the data gathered for the master node.
If the calculated size of some node is much larger than for the other nodes, then it is recommended to set the sql_plan_cache size for this node on host level and to analyze with application experts if tables should be re-distributed for improved load balancing.
5. Test and validate new configuration
Schedule a follow-up on one of the following days to check if decision criteria from step 4. Use the SQL statement from step 2 and review the current alerts for new occurrences of alert 58.If decision criteria suggest that plan cache size is still too small, then increase Plan_cache_size step-by-step and re-iterate. So far no side effects have been reported when following this approach.
6. If you face continued issues with regard to plan cache size the following activities are recommended
- Check for Knowledge Base Articles
- Search for recent SAP Notes with keyword 'SQL PLAN CACHE' in Application Components HAN-DB* on component SV-BO-DB-HAN
- Use SQL Query HANA_SQL_StatementHash_SQLCache_History from SAP Knowledge Base Article 1969700 to identify times when large numbers of Plan Cache Evictions occur. Analyze System Load for identified peak times and check what applications are running during this time. Investigate options to optimize the performance of these applications.
For more detailed guidance please refer to attached document and Demo recording
>>>>>>>>>>>>>>>>>>>>END OF KBA<<<<<<<<<<<<<<<<<<<<<<<<ACTIVITY: Check HANA Studio - Alerts:
ACTIVITY: Set Alert Filter to Alert ID 58 and extend timeline to approximately 4 weeks
SQL Query Result:
ACTIVITY: Apply Parameter Changes:
Number of SQL connections:
You can get the # of SQL connections from the administration tool of HANA studio. PATH: Performance -> Load -> SQL Open Connections -> AverageIn this case, 10 is the number of SQL Connections for plan cache size estimation. It is recommended not to change Plan Cache Size to a value lower than the default.
See Also
SAP Notes
2040002 - Size recommendation for the HANA SQL Plan Cache1969700 - SQL Statement Collection for SAP HANA
2000000 - FAQ: SAP HANA Performance Optimization
SAP HANA Guides
The SAP HANA Performance Tuning Guide contains comprehensive information how to optimize performance of SAP HANA databases:https://help.sap.com/hana/SAP_HANA_Performance_Analysis_Guide_en.pdf
Additional details can be found in:
SAP HANA Administration Guide, HANA Troubleshooting Resources, SAP HANA Performance Optimization
Keywords
SAP HANA - Alerts - SQL PLan Cache - SQL Monitor - Operations Recommendation - #OpsRec-HANA
No comments:
Post a Comment