Recientemente he realizado múltiples pruebas con la instancia de SAP HANA en la que he podido probar múltiples herramientas y funciones, específicamente la nueva BS S/4HANA y me he enfrentado con muchos problemas con el uso de memoria ya que no tengo de sobra y siempre termino reiniciando el sistema para recuperar la memoria de nuevo, pero aquí muestro algunos datos que pueden ser de utilidad a la hora de analizar el consumo de memoria, principalmente a identificar los principales causantes del consumo e intentar mitigarlo de ser posible, utilizando HANA Studio o SQL
Abrir el Cockpit
Donde se mostrara lo siguiente y debe ser elegida la opción Used Memory
Aquí tenemos un análisis de los diferentes KPI referentes a Memoria, el valor máximo, promedio el último y una pequeña grafica que lo ejemplifica y compara
Ahora bien para ver a detalle las tablas que están consumiendo esta memoria, regresar a home y utilizar User Tables
Este muestra un mayor detalle pero por tablas y como estas están consumiendo memoria, como se aprecia en Memory Used y después en las diferentes tablas donde se aprecia claramente las tablas con mayor volumen
Ahora bien si se desea ver más gráficamente y en general el consumo regresamos a HOME y utilizar la opción Manage Services
Y al seleccionar algún servicio en la columna de Used Memory se accede a un reporte más gráfico:
El cual permite entender más gráficamente el consumo con lo que se facilita el análisis y se pude determinar una posible solucion
Para finalizar también es posible directamente en HANA Studio utilizar el podero SQL para obtener esta información, mediante el uso de SQL Console por ejemplo:
SELECT
NAME,
LPAD(ROUND(PRODUCT_LIMIT), 9) TOTAL_GB,
' ' DETAIL_GB,
' ' DETAIL2_GB
FROM
M_LICENSE
UNION
ALL
( SELECT
' ',
' ',
' ',
' '
FROM
DUMMY
)
UNION
ALL
( SELECT
MAP(ROW_NUMBER () OVER (ORDER
BY LH.PERIOD_START_TIME), 1, 'License usage', ' '),
MAP(ROW_NUMBER () OVER (ORDER
BY LH.PERIOD_START_TIME), 1, LPAD(ROUND(L.PRODUCT_USAGE), 9), ' '),
LPAD(ROUND(LH.PRODUCT_USAGE), 9) || ' (' || TO_CHAR(PERIOD_START_TIME, 'YYYY/MM/DD') ||
'-' || TO_CHAR(PERIOD_END_TIME, 'YYYY/MM/DD') || ')',
' '
FROM
M_LICENSE L,
M_LICENSE_USAGE_HISTORY LH
ORDER
BY
PERIOD_START_TIME
)
UNION
ALL
( SELECT
' ',
' ',
' ',
' '
FROM
DUMMY
)
UNION
ALL
( SELECT
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, 'Physical memory (available)', ' '),
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, LPAD(ROUND(SUM(TO_NUMBER(VALUE)) OVER () / 1024 / 1024 / 1024), 9), ' '),
LPAD(ROUND(TO_NUMBER(VALUE) / 1024 / 1024 / 1024), 9) || ' (' || HOST || ')',
' '
FROM
M_HOST_INFORMATION
WHERE
KEY = 'mem_phys'
ORDER
BY
HOST
)
UNION
ALL
( SELECT
' ',
' ',
' ',
' '
FROM
DUMMY
)
UNION
ALL
( SELECT
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, 'Physical memory (used)', ' '),
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, LPAD(ROUND(SUM(USED_PHYSICAL_MEMORY) OVER () / 1024 / 1024 / 1024), 9), ' '),
LPAD(ROUND(USED_PHYSICAL_MEMORY / 1024 / 1024 / 1024), 9) || ' (' || HOST || ')',
' '
FROM
M_HOST_RESOURCE_UTILIZATION
ORDER
BY
HOST
)
UNION
ALL
( SELECT
' ',
' ',
' ',
' '
FROM
DUMMY
)
UNION
ALL
( SELECT
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, 'Global allocation limit', ' ') NAME,
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, LPAD(ROUND(SUM(ALLOCATION_LIMIT / 1024 / 1024 / 1024) OVER ()), 9), ' ') TOTAL_GB,
LPAD(ROUND(ALLOCATION_LIMIT / 1024 / 1024 / 1024), 9) || ' (' || HOST || ')' DETAIL_GB,
' ' DETAIL2_GB
FROM
M_HOST_RESOURCE_UTILIZATION
)
UNION
ALL
( SELECT
' ',
' ',
' ',
' '
FROM
DUMMY
)
UNION
ALL
( SELECT
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, 'HANA instance memory (allocated)', ' '),
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, LPAD(ROUND(SUM(INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE) OVER () / 1024 / 1024 / 1024), 9), ' '),
LPAD(ROUND(INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE / 1024 / 1024 / 1024), 9) || ' (' || HOST || ')',
' '
FROM
M_HOST_RESOURCE_UTILIZATION
ORDER
BY
HOST
)
UNION
ALL
( SELECT
' ',
' ',
' ',
' '
FROM
DUMMY
)
UNION
ALL
( SELECT
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, 'HANA instance memory (peak used)', ' '),
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, LPAD(ROUND(TOTAL_SIZE / 1024 / 1024 / 1024), 9), ' '),
LPAD(ROUND(HOST_SIZE / 1024 / 1024 / 1024), 9) || ' (' || HOST || ')',
' '
FROM
( SELECT
HOST,
HOST_SIZE,
GREATEST(TOTAL_SIZE, MAX(HOST_SIZE) OVER ()) TOTAL_SIZE
FROM
( SELECT
HOST,
HOST_SIZE,
IFNULL(TOTAL_SIZE, SUM(HOST_SIZE) OVER ()) TOTAL_SIZE
FROM
( SELECT
IFNULL(CURR_HOST, HIST_HOST) HOST,
GREATEST(IFNULL(CURR_SIZE, 0), IFNULL(HIST_SIZE, 0)) HOST_SIZE,
TOTAL_SIZE
FROM
( SELECT
HOST CURR_HOST,
INSTANCE_TOTAL_MEMORY_PEAK_USED_SIZE CURR_SIZE
FROM
M_HOST_RESOURCE_UTILIZATION
) C FULL
OUTER
JOIN
( SELECT
HOST HIST_HOST,
MAX(INSTANCE_TOTAL_MEMORY_USED_SIZE) HIST_SIZE,
TOTAL_SIZE
FROM
_SYS_STATISTICS.HOST_RESOURCE_UTILIZATION_STATISTICS,
( SELECT
TOP 1 SUM(INSTANCE_TOTAL_MEMORY_USED_SIZE) TOTAL_SIZE
FROM
_SYS_STATISTICS.HOST_RESOURCE_UTILIZATION_STATISTICS
GROUP
BY
SNAPSHOT_ID
ORDER
BY
SUM(INSTANCE_TOTAL_MEMORY_USED_SIZE) DESC
) T
GROUP
BY
TOTAL_SIZE,
HOST
) H ON
C.CURR_HOST = H.HIST_HOST
)
)
)
ORDER
BY
HOST
)
UNION
ALL
( SELECT
' ',
' ',
' ',
' '
FROM
DUMMY
)
UNION
ALL
( SELECT
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, 'HANA instance memory (used)', ' '),
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, LPAD(ROUND(SUM(INSTANCE_TOTAL_MEMORY_USED_SIZE) OVER () / 1024 / 1024 / 1024), 9), ' '),
LPAD(ROUND(INSTANCE_TOTAL_MEMORY_USED_SIZE / 1024 / 1024 / 1024), 9) || ' (' || HOST || ')',
' '
FROM
M_HOST_RESOURCE_UTILIZATION
ORDER
BY
HOST
)
UNION
ALL
( SELECT
' ',
' ',
' ',
' '
FROM
DUMMY
)
UNION
ALL
( SELECT
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, 'HANA shared memory', ' '),
MAP(ROW_NUMBER () OVER (ORDER
BY HOST), 1, LPAD(ROUND(SUM(INSTANCE_SHARED_MEMORY_ALLOCATED_SIZE) OVER () / 1024 / 1024 / 1024), 9), ' '),
LPAD(ROUND(INSTANCE_SHARED_MEMORY_ALLOCATED_SIZE / 1024 / 1024 / 1024), 9) || ' (' || HOST || ')',
' '
FROM
M_HOST_RESOURCE_UTILIZATION
ORDER
BY
HOST
)
UNION
ALL
( SELECT
' ',
' ',
' ',
' '
FROM
DUMMY
)
UNION
ALL
( SELECT
NAME,
TOTAL_GB,
DETAIL_GB,
DETAIL2_GB
FROM
( SELECT
MAP(ROW_NUMBER () OVER (ORDER
BY HOST, EXCLUSIVE_SIZE_IN_USE DESC), 1, 'HANA heap memory (used)', ' ') NAME,
MAP(ROW_NUMBER () OVER (ORDER
BY HOST, EXCLUSIVE_SIZE_IN_USE DESC), 1, LPAD(ROUND(SUM(EXCLUSIVE_SIZE_IN_USE) OVER () / 1024 / 1024 / 1024), 9), ' ') TOTAL_GB,
MAP(ROW_NUMBER () OVER (PARTITION
BY HOST ORDER
BY EXCLUSIVE_SIZE_IN_USE DESC), 1,
LPAD(ROUND(SUM(EXCLUSIVE_SIZE_IN_USE) OVER (PARTITION
BY HOST) / 1024 / 1024 / 1024), 9) || ' (' || HOST || ')', ' ') DETAIL_GB,
LPAD(ROUND(SUM(EXCLUSIVE_SIZE_IN_USE) / 1024 / 1024 / 1024), 9) || ' (' || CATEGORY || ')' DETAIL2_GB,
ROW_NUMBER () OVER (PARTITION
BY HOST ORDER
BY EXCLUSIVE_SIZE_IN_USE DESC) ROW_NUM
FROM
( SELECT
HOST,
CATEGORY,
SUM(EXCLUSIVE_SIZE_IN_USE) EXCLUSIVE_SIZE_IN_USE
FROM
M_HEAP_MEMORY
GROUP
BY
HOST,
CATEGORY
)
GROUP
BY
HOST,
CATEGORY,
EXCLUSIVE_SIZE_IN_USE
ORDER
BY
HOST,
EXCLUSIVE_SIZE_IN_USE DESC
)
WHERE
ROW_NUM <= 10
)
UNION
ALL
( SELECT
' ',
' ',
' ',
' '
FROM
DUMMY
)
UNION
ALL
( SELECT
NAME,
TOTAL_GB,
DETAIL_GB,
DETAIL2_GB
FROM
( SELECT
MAP(ROW_NUMBER () OVER (ORDER
BY HOST, MEMORY_SIZE_IN_TOTAL DESC), 1, 'Column store size', ' ') NAME,
MAP(ROW_NUMBER () OVER (ORDER
BY HOST, MEMORY_SIZE_IN_TOTAL DESC), 1, LPAD(ROUND(SUM(MEMORY_SIZE_IN_TOTAL) OVER () / 1024 / 1024 / 1024), 9), ' ') TOTAL_GB,
MAP(ROW_NUMBER () OVER (PARTITION
BY HOST ORDER
BY MEMORY_SIZE_IN_TOTAL DESC), 1,
LPAD(ROUND(SUM(MEMORY_SIZE_IN_TOTAL) OVER (PARTITION
BY HOST) / 1024 / 1024 / 1024), 9) || ' (' || HOST || ')', ' ') DETAIL_GB,
LPAD(ROUND(SUM(MEMORY_SIZE_IN_TOTAL) / 1024 / 1024 / 1024), 9) || ' (' || TABLE_NAME || ')' DETAIL2_GB,
ROW_NUMBER () OVER (PARTITION
BY HOST ORDER
BY MEMORY_SIZE_IN_TOTAL DESC) ROW_NUM,
ROUND(SUM(MEMORY_SIZE_IN_TOTAL) / 1024 / 1024 / 1024) TABLE_SIZE_GB
FROM
( SELECT
HOST,
TABLE_NAME,
SUM(MEMORY_SIZE_IN_TOTAL) MEMORY_SIZE_IN_TOTAL
FROM
M_CS_TABLES
GROUP
BY
HOST,
TABLE_NAME
)
GROUP
BY
HOST,
TABLE_NAME,
MEMORY_SIZE_IN_TOTAL
ORDER
BY
HOST,
MEMORY_SIZE_IN_TOTAL DESC
)
WHERE
TABLE_SIZE_GB > 0 AND
ROW_NUM <= 10
)
UNION
ALL
( SELECT
' ',
' ',
' ',
' '
FROM
DUMMY
)
UNION
ALL
( SELECT
MAP(ROW_NUM_GLOBAL, 1, NAME, ' ') NAME,
MAP(ROW_NUM_GLOBAL, 1, TOTAL_GB, ' ') TOTAL_GB,
DETAIL_GB,
DETAIL2_GB
FROM
( SELECT
'Row store size' NAME,
LPAD(ROUND(AVG(R.ROW_STORE_USED_SIZE) OVER () / 1024 / 1024 / 1024), 9) TOTAL_GB,
MAP(ROW_NUMBER () OVER (PARTITION
BY HOST ORDER
BY
SUM(T.TABLE_SIZE) DESC), 1,
LPAD(ROUND(SUM(SUM(TABLE_SIZE)) OVER (PARTITION
BY HOST) / 1024 / 1024 / 1024), 9) || ' (' || HOST || ')', ' ') DETAIL_GB,
LPAD(ROUND(SUM(TABLE_SIZE) / 1024 / 1024 / 1024), 9) || ' (' || TABLE_NAME || ')' DETAIL2_GB,
ROW_NUMBER () OVER (PARTITION
BY HOST ORDER
BY
SUM(TABLE_SIZE) DESC) ROW_NUM,
ROW_NUMBER () OVER (ORDER
BY
SUM(TABLE_SIZE) DESC) ROW_NUM_GLOBAL,
ROUND(SUM(TABLE_SIZE) / 1024 / 1024 / 1024) TABLE_SIZE_GB
FROM
( SELECT
HOST,
TABLE_NAME,
ALLOCATED_FIXED_PART_SIZE + ALLOCATED_VARIABLE_PART_SIZE TABLE_SIZE
FROM
M_RS_TABLES
UNION
ALL
( SELECT
HOST,
TABLE_NAME,
INDEX_SIZE TABLE_SIZE
FROM
M_RS_INDEXES
)
) T,
( SELECT
SUM(USED_SIZE) ROW_STORE_USED_SIZE FROM M_RS_MEMORY ) R
GROUP
BY
T.HOST,
T.TABLE_NAME,
R.ROW_STORE_USED_SIZE
ORDER
BY
SUM(T.TABLE_SIZE) DESC
)
WHERE
TABLE_SIZE_GB > 0 AND
ROW_NUM <= 10
)
UNION
ALL
( SELECT TOP 1
' ',
' ',
' ',
' '
FROM
M_LIVECACHE_CONTAINER_STATISTICS
)
UNION
ALL
( SELECT
MAP(ROW_NUM_GLOBAL, 1, NAME, ' ') NAME,
MAP(ROW_NUM_GLOBAL, 1, TOTAL_GB, ' ') TOTAL_GB,
DETAIL_GB,
DETAIL2_GB
FROM
( SELECT
'liveCache size' NAME,
LPAD(ROUND(SUM(SUM(PAGE_SIZE_BYTE)) OVER () / 1024 / 1024 / 1024), 9) TOTAL_GB,
MAP(ROW_NUMBER () OVER (PARTITION
BY HOST ORDER
BY
SUM(PAGE_SIZE_BYTE) DESC), 1,
LPAD(ROUND(SUM(SUM(PAGE_SIZE_BYTE)) OVER (PARTITION
BY HOST) / 1024 / 1024 / 1024), 9) || ' (' || HOST || ')', ' ') DETAIL_GB,
LPAD(ROUND(SUM(PAGE_SIZE_BYTE) / 1024 / 1024 / 1024), 9) || ' (' || OMS_CLASS_NAME || ')' DETAIL2_GB,
ROW_NUMBER () OVER (PARTITION
BY HOST ORDER
BY
SUM(PAGE_SIZE_BYTE) DESC) ROW_NUM,
ROW_NUMBER () OVER (ORDER
BY
SUM(PAGE_SIZE_BYTE) DESC) ROW_NUM_GLOBAL,
ROUND(SUM(PAGE_SIZE_BYTE) / 1024 / 1024 / 1024) CONTAINER_SIZE_GB
FROM
( SELECT
HOST,
OMS_CLASS_NAME,
SUM(PAGE_SIZE_SUM) PAGE_SIZE_BYTE
FROM
M_LIVECACHE_CONTAINER_STATISTICS
GROUP
BY
HOST,
OMS_CLASS_NAME
)
GROUP
BY
HOST,
OMS_CLASS_NAME
ORDER
BY
SUM(PAGE_SIZE_BYTE) DESC
)
WHERE
CONTAINER_SIZE_GB > 0 AND
ROW_NUM <= 10
)
UNION
ALL
( SELECT
' ',
' ',
' ',
' '
FROM
DUMMY
)
UNION
ALL
( SELECT
NAME,
TOTAL_GB,
DETAIL_GB,
DETAIL2_GB
FROM
( SELECT
MAP(ROW_NUMBER () OVER (ORDER
BY
SUM(TP.TABLE_SIZE) DESC), 1, 'Disk size', ' ') NAME,
MAP(ROW_NUMBER () OVER (ORDER
BY
SUM(TP.TABLE_SIZE) DESC), 1, LPAD(ROUND(SUM(TP.TABLE_SIZE) OVER () / 1024 / 1024 / 1024), 9), ' ') TOTAL_GB,
MAP(ROW_NUMBER () OVER (ORDER
BY
SUM(TP.TABLE_SIZE) DESC), 1,
LPAD(ROUND(SUM(TP.TABLE_SIZE) OVER () / 1024 / 1024 / 1024), 9) || ' (global)', ' ') DETAIL_GB,
LPAD(ROUND(TP.TABLE_SIZE / 1024 / 1024 / 1024), 9) || ' (' || TP.TABLE_NAME || ')' DETAIL2_GB,
ROW_NUMBER () OVER (ORDER
BY TP.TABLE_SIZE DESC) ROW_NUM,
ROUND(TP.TABLE_SIZE / 1024 / 1024 / 1024) TABLE_SIZE_GB
FROM
( SELECT
TABLE_NAME,
SUM(DISK_SIZE) TABLE_SIZE
FROM
M_TABLE_PERSISTENCE_STATISTICS
GROUP
BY
TABLE_NAME
) TP
GROUP
BY
TP.TABLE_NAME,
TP.TABLE_SIZE
ORDER
BY
SUM(TP.TABLE_SIZE) DESC
)
WHERE
TABLE_SIZE_GB > 0 AND
ROW_NUM <= 10
)
Para al final obtener el siguiente resultado donde se aprecia la misma información que obtuvimos por el Cockpit pero
sin salir de HANA Studio
Ahora con esto ya es posible intentar deshabilitar algunas tablas que ya identificamos como las que más demandan memoria.