====== 1. HANA performance tweaks ======

===== 1.1. Produmex WMS stored procedure recompilation on HANA =====

==== Problem description ====

As of SAP HANA 122.05, it can happen that the HANA server periodically recompiles some of the Produmex WMS stored procedures (namely: ''PMX_SP_TransactionNotificationStock'' and ''PMX_SP_TransactionNotificationChecks''). This recompilation is quite time consuming: it can take between 25 and 40 seconds per stored procedure, depending on the HANA server.

These recompilations occur when the statistics related to these stored procedures change. When the statistics change too frequently, the recompilations also occur very frequently, thus generating a considerable performance issue.

==== How to identify the problem ====

If there are some performance issues on a HANA system when running SBO and Produmex WMS, it is of crucial importance to first clearly identify the root cause of this performance issue. This section only focuses on one very specific issue, which is the frequent recompilation of the 2 abovementioned Produmex stored procedures.

The recommended method to identify this issue is to monitor the threads on the HANA server while a long processing occurs in SBO or Produmex WMS.

The threads can be found the following way in SAP HANA Studio:
  * Double-click on the server
  * Open the 'Performance' tab
  * Open the 'Threads' sub-tab
  * Enable auto-refresh with 5 seconds delay

{{ :implementation:hana_sprecompile_threads01.png |}}

Then start working with SBO or Produmex WMS, in a way that will trigger the slow process. While waiting for the slow process to occur, look if a line meeting the following criteria appears:
  * Thread Method: COMPILE
  * Thread Detail: (...)PMX_SP_TransactionNotification(...)
  * Duration (ms): should be growing up to at least 20,000 ms

Here's an example of such a recompilation as it can be seen in the threads list:

{{ :implementation:hana_sprecompile_threads02.png |}}

==== Resolution ====

In order to resolve this problem, you can upgrade to SAP HANA 122.09 or higher. That version includes a fix where statistics changes will not trigger a recompilation of stored procedures.

If it's not possible to upgrade to SAP HANA 122.09 or higher, then you can also alter some HANA server settings that will have the same effect: statistics changes will not trigger a recomiplation of stored procedures.

First, check if the setting already exists and has the correct value. In SAP HANA Studio, in the 'Configuration' tab, check if the ''cs_statistics_cache_invalidation_threshold_factor'' setting exists. It should be within indexserver.ini, under the 'cache' section.

{{ :implementation:hana_sprecompile_setting01.png |}}

If the setting doesn't exist yet, or doesn't have the value '0', then execute the following command, which will create and update the setting:
<code sql>ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET ('cache','cs_statistics_cache_invalidation_threshold_factor') = '0' WITH RECONFIGURE; </code>
Additionally, the ''with reconfigure'' part automatically applies the changes without needing to restart the HANA server.

The expected result of that command is:
<code>Statement 'ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET ...' 
successfully executed in 410 ms 501 µs  (server processing time: 398 ms 15 µs) - Rows Affected: 0</code>

If the setting already exists and has the value '0', then you don't have to modify it. In that situation, if you're still suffering from the recompilation of stored procedures as described above, try to restart the HANA server.

===== 1.2. Optimizing plan cache size =====

A full and comprehensive guide for setting the plan cache size can be found at [[https://wiki.scn.sap.com/wiki/display/SAPHANA/Guide+to+Tuning+SQL+Plan+Cache|this link]].

Please note that in the case of SAP Business One, the "non-ABAP" instructions should be followed.

===== 1.3. Slow connection to the SAP HANA server =====
{{page>implementation:hanaperformancegeneric&noheader&noeditbtn}}