Press "Enter" to skip to content

HANA Analizando consumo de Memoria

snatic_k8ot5v 0

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.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *