As of SAP HANA 122.05, it can happen that the HANA server periodically recompiles the following Produmex WMS stored procedures:
PMX_SP_TransactionNotificationStockPMX_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
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 does not exist yet, or doesn't have the value '0', 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.
Optimizing plan cache size
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.
Comments
0 comments
Please sign in to leave a comment.