HANA Alerts related to SQL Plan Cache

How to handle HANA Alerts And how it is related to SQL Plan Cache?

End-Users may experience performance issues due to:

Frequent Eviction of SQL Execution Plans from Cache and
SQL Statement Recompilation

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 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 '%'.

SELECT
SO.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
WHERE
SO.HOST LIKE BI.HOST AND
TO_CHAR(SO.PORT) LIKE BI.PORT AND
H.HOST = SO.HOST AND
H.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 2040002 for latest recommendations (This KBA refers to version 2).
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 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 s 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.