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 CapacitySuggested Xmx Value
8 GB - 12 GB1.5 GB
12 GB - 16 GB2 GB
16 GB - 24 GB3 GB
24 GB - 32 GB4 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:

  1. Go to Start Menu > Run, and then type regedit
  2. In the Registry Editor, find (Ctrl+F) Apache Software Foundation.
  3. Expand Apache Software Foundation:
    > Procrun 2.0
    > precise_hanalyzer_deploy_localsystem
    > Parameters
  4.  Select Java.
  5. On the right hand panel, double-click Options, and then add the following new line in the Value Data field:
    -Xmx<MaxHeapSize>
    Examples:
    -Xmx2g
    -Xmx4096m
  6. Restart the Collector Service.


IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal