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:
- Frequent Eviction of SQL Execution Plans from Cache and
- SQL Statement Recompilation
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.
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.
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
To adjust the default value of plan cache size go to the Configuration Tab in HANA Studio and navigate to
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.
Contact SAP Support and attach the details of your analysis and
changes (information and screenshots). Use Application Component
SV-BO-DB-HAN when creating the incident.
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
ACTIVITY: SQL Query:
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
-> Average
In 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.
SAP Notes
2040002 - Size recommendation for the HANA SQL Plan Cache
1969700 - 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
SAP HANA - Alerts - SQL PLan Cache - SQL Monitor - Operations Recommendation - #OpsRec-HANA
