Skip to end of metadata
Go to start of metadata

Purpose

The purpose of this guide is to explain how to optimally tune sql plan cache in HANA in order to help overcome the high number of execution plan evictions and recompiliations

What is SQL PLAN CACHE?

SQL Plan Cache is an area allocated in memory where the execution plans of sql queries are parsed and stored.   If it is undersized in HANA, this can result in Alert 58: Plan Cache Size alerts appearing in the Alerts tab of HANA Studio.

How to Tune SQL Plan Cache

  1. Check that the parameter 'plan_cache_enabled' is set to true in the indexserver ini file
  2. If it is not enabled then use the following command to enable it:
    ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’,’system’) SET(‘sql’, ‘plan_cache_enabled’) = ‘True’ WITH RECONFIGURE
  3. Query the view M_SQL_PLAN_CACHE_OVERVIEW to find the current value of PLAN_CACHE_SIZE and PLAN_CACHE_CAPACITY (Note that this view shows lists values for each host in the system landscape)
  4. Observe the PLAN_CACHE_HIT_RATIO value from the view. (Note that it is desirable to have this value over 90% or .9)

  5. Observe the EVICTED_PLAN_COUNT value from the view (Note that a high number of evictions along with a low hit ratio could indicate a potential tuning requirement)
  6. Identify whether your system is an ABAP or Non-ABAP system
    1. If you are using an ABAP System then the following formula is recommended for the optimal plan cache size:

      Number of SQL Connections * 15MB

      You can check # of SQL connection by CONNECTION_ID > 0 AND CREATED_BY = 'Session' in M_CONNECTIONS monitoring view.
      select COUNT(CONNECTION_ID) from SYS.M_CONNECTIONS WHERE CREATED_BY = 'Session';

b. If you are using a Non-ABAP System, then the following formula is recommended:

    plan_cache_size = 0.90 x (current PLAN_CACHE_CAPACITY)/(current PLAN_CACHE_HIT_RATIO)

c  If you are using both ABAP and Non-ABAP applications then use the formula for the ABAP System

7. To change the value of PLAN_CACHE_SIZE, use the following command:

ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’,’system’) SET(‘sql’, ‘plan_cache_size’) = ‘<DESIRED_SIZE_IN_BYTES>’ WITH RECONFIGURE


Points to Note

A. SQL Plan Cache values of over 20GB is not recommended and should be avoided

B. The usage of bind variables must be present in the application code, otherwise, the change to the plan_cache_size will have no affect.

C. A restart of HANA database may be required in order for changes to come into affect, alternatively, by resetting the M_SQL_PLAN_CACHE_RESET view, this can reset the values in   

    order to gather correct statistics.

                    ALTER SYSTEM RESET MONITORING VIEW SYS.M_SQL_PLAN_CACHE_RESET;


Related SAP Notes / KBA's

  • SAP Note 1977253 - How to handle HANA Alert 58 - 'Plan Cache Size'
  • SAP Note 1999993 - SAP HANA Mini Checks 
  • SAP Note 2040002 - Size recommendation for the HANA SQL Plan Cache
  • SAP Note 2000002 - FAQ: SAP HANA SQL Optimization
  • SAP Note 2124112  - FAQ: SAP HANA Parsing 

 

Related Documents

SAP HANA SQL and System Views Reference

SAP HANA Administration Guide


 



 

 

 

 

 

 

 

  • No labels

1 Comment

  1. Hi - I think this doc is outdated.  There are many production HANA customers who are running with SQLPlan Caches greater than 20 Gb.