User Tools

Site Tools


implementation:performance:main

Database Performance Optimization

1. HANA

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

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:

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.

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:

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET ('cache','cs_statistics_cache_invalidation_threshold_factor') = '0' WITH RECONFIGURE; 

Additionally, the with reconfigure part automatically applies the changes without needing to restart the HANA server.

The expected result of that command is:

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

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 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

In some environments, you could notice a delay of about 1 second in the direct database connection to the SAP HANA server. This usually results in a very poor overall performance of the Produmex add-ons, without any particular server load.

Even though the delay is only 1 second, this delay will occur at every single database connection, and it adds up a lot to generate tremendous performance issues.

On such environments, the solution is to add the SAP HANA server's IP address mapping to the affected server's hosts file. For example, in the C:\Windows\System32\drivers\etc\hosts file, add the following line:

192.168.1.33 hanaserver

Our recommendation is to always add the SAP HANA server's IP address mapping to the hosts file, on all systems that connect to the SAP HANA server, even if these systems are not affected by this particular issue.

This issue is documented in SAP Note 2525371

implementation/performance/main.txt · Last modified: 2018/02/26 15:11 by csuhaa