Skip to Content
0
Former Member
May 11, 2016 at 11:52 PM

host_cs_unloads taking a lot of memory

58 Views

Hi All,

I have been looking at disabling the data collection related to host_cs_unloads.

When checked this note 2084747 - How-To: Disabling Memory-intensive Data Collections of standalone SAP HANA Statistics Server

The procedure would be as below so should i follow the same procedure for host_cs_unloads as well?

  • Activate editing for the statisticsserver: ALTER SYSTEM ALTER CONFIGURATION ('statisticsserver.ini', 'SYSTEM') SET ('statisticsserver_general','editing')='yes' WITH RECONFIGURE;
  • Determine the history selection query for the involved table that is found in one of the following parameters (depending on the view <table> either represents the full table name, e.g. 'HOST_BLOCKED_TRANSACTIONS', or the restricted table name without leading 'HOST_', e.g. 'SQL_PLAN_CACHE'): statisticsserver.ini -> [statisticsserver_view_STAT_VIEW_<table>] -> query
    statisticsserver.ini -> [statisticsserver_view_<table>] -> query
  • Adjust this query so that it is no longer executed (e.g. by adding "AND 1 = 0" at the end) and update the "query" parameter with this new setting. Be aware that single quotes have to be replaced with double quotes if you want to adjust the setting via ALTER SYSTEM ALTER CONFIGURATION. Example:

Capture.JPG

  • For HOST_SQL_PLAN_CACHE it is usually enough to make sure that useless records with EXECUTION_COUNT = 0 are not inserted (they may be responsible for 90 % of the overall table records). This can be achieved by adding "WHERE EXECUTION_COUNT > 0" at the end of the command. Be aware that single quotes have to be replaced with double quotes if you want to adjust the setting via ALTER SYSTEM ALTER CONFIGURATION. Example:

host_sql_plan_cache.JPG

  • Disable editing for the statisticsserver:ALTER SYSTEM ALTER CONFIGURATION ('statisticsserver.ini','SYSTEM') UNSET ('statisticsserver_general','editing') WITH RECONFIGURE;