Configuring the WASH Collector when the Total HANA SQL Plan Cache capacity is greater than 8 GB
If the sum of the SQL Plan Cache capacity of all the HANA instances monitored is more than 8GB, you must set the Maximum Heap size (Xmx value) for the collector. The following table includes suggested Xmx values to be set for the range of HANA SQL Plan Cache Capacities.
|Range of Total Plan Cache Capacity||Suggested Xmx Value|
|8 GB - 12 GB||1.5 GB|
|12 GB - 16 GB||2 GB|
|16 GB - 24 GB||3 GB|
|24 GB - 32 GB||4 GB|
By default, the Xmx Value is 25% of the physical memory of the machine. Hence it is recommended to have the physical memory of at least 1 GB more than the custom Xmx value.
How to check the SQL Plan Cache Capacity of HANA Server
To check the SQL Plan Cache capacity of a HANA server, execute the following on an SAP HANA Client:
SELECT plan_cache_capacity FROM M_SQL_PLAN_CACHE_OVERVIEW
The result provides the SQL Plan Cache capacity in bytes.
How to set the Xmx value of the collector
The Xmx value should be set after you install Workload Analysis for SAP HANA.
To set the Xmx value:
- Go to Start Menu > Run, and then type
- In the Registry Editor, find (Ctrl+F) Apache Software Foundation.
- Expand Apache Software Foundation:
> Procrun 2.0
- Select Java.
- On the right hand panel, double-click Options, and then add the following new line in the Value Data field:
- Restart the Collector Service.