select top 1 HOST, SERVER_TIMESTAMP, round(TOTAL_MEMORY_USED_SIZE/1024/1024/1024, 2) as "Used Memory GB"
from _SYS_STATISTICS.HOST_SERVICE_MEMORY where SERVICE_NAME = 'indexserver'
order by TOTAL_MEMORY_USED_SIZE desc

select * from _SYS_STATISTICS.HOST_SERVICE_MEMORY where SERVICE_NAME = 'indexserver'

-- 전체 Allocation 대비 Used Memory % (분당 5개의 snapsbot 있는듯)
-- MEMORY_USED : The memory used by the service
select to_char(time, 'YYYY-MM-DD'),
round(avg(memory_used/1024/1024/1024/1024),2) as "Used (GB)",
round(avg(memory_allocation_limit/1024/1024/1024),2) as "Alloc (GB)",
round(avg(memory_used/memory_allocation_limit*100),2) as "PERC (%)"
from m_load_history_service group by to_char(time, 'YYYY-MM-DD')

--select to_char(time, 'YYYY-MM-DD'), avg(round(memory_used/memory_allocation_limit,2)*100) as "PERC (%)" from m_load_history_service group by to_char(time, 'YYYY-MM-DD')

-- 물리 메모리 대비 HANA가 사용하는 사이즈
-- MEMORY_USED : Memory used for all SAP HANA processes
-- MEMORY_SIZE : Physical memory size
--select to_char(time, 'YYYY-MM-DD'), avg(round(memory_used/memory_size,2)*100) from m_load_history_host group by to_char(time, 'YYYY-MM-DD')

-- History
--select to_char(time, 'YYYY-MM-DD'), round(avg(memory_used/memory_size*100),2), round(avg(memory_used),2), round(avg(memory_size),2) from m_load_history_host group by to_char(time, 'YYYY-MM-DD') order by 1
--select to_char(time, 'YYYY-MM-DD'), round(avg(memory_used/memory_size*100),2), round(avg(memory_used),2), round(avg(memory_size),2) from m_load_history_host where time between add_days(CURRENT_DATE, -7) and current_date group by to_char(time, 'YYYY-MM-DD')
--select to_char(time, 'YYYY-MM-DD'), round(avg(memory_used/memory_size*100),2) from m_load_history_host where time between add_days(CURRENT_DATE, -7) and current_date group by to_char(time, 'YYYY-MM-DD')

-- History peak memory
select to_char(server_timestamp, 'YYYY-MM-DD'), round(max(TOTAL_MEMORY_USED_SIZE/1024/1024/1024), 2)
from _SYS_STATISTICS.HOST_SERVICE_MEMORY
where SERVICE_NAME = 'indexserver'
group by to_char(server_timestamp, 'YYYY-MM-DD')
order by round(max(TOTAL_MEMORY_USED_SIZE/1024/1024/1024), 2) desc

-- top peak memory
select top 500 HOST, SERVER_TIMESTAMP,
       round(INSTANCE_TOTAL_MEMORY_USED_SIZE/1024/1024/1024, 2) as "Peak Used GB"
from _SYS_STATISTICS.HOST_RESOURCE_UTILIZATION_STATISTICS
 where HOST = ‘node01’ and SERVER_TIMESTAMP
order by "Peak Used GB" desc

-- top peak memory
select HOST, 
       round(INSTANCE_TOTAL_MEMORY_PEAK_USED_SIZE/(1024*1024*1024), 2) as "Peak Used Memory GB"
from M_HOST_RESOURCE_UTILIZATION;

-- allocation limit
SELECT HOST, 
       round(ALLOCATION_LIMIT/1024/1024/1024, 2) AS "Allocation Limit GB" 
FROM PUBLIC.M_HOST_RESOURCE_UTILIZATION;


-- Get the 10 top peak used memory details
-- Result is sorted by memory size.  ( it is not a sequential date and time list )

select top 30 HOST, 
       SERVER_TIMESTAMP, 
       round(INSTANCE_TOTAL_MEMORY_USED_SIZE/(1024*1024*1024), 2) as "Used Memory GB" 
from _SYS_STATISTICS.HOST_RESOURCE_UTILIZATION_STATISTICS
where hour(SERVER_TIMESTAMP) = 7 and minute(SERVER_TIMESTAMP) = 0 
order by INSTANCE_TOTAL_MEMORY_USED_SIZE;


-- 어제 사용량
--select to_char(time, 'YYYY-MM-DD HH24:MI'), round(avg(memory_used/memory_size*100),2), round(avg(memory_used),2), round(avg(memory_size),2) from m_load_history_host where to_char(time, 'YYYY-MM-DD') = add_days(CURRENT_DATE, -1) group by to_char(time, 'YYYY-MM-DD HH24:MI')

--select sum(memory_size_in_main+memory_size_in_delta)/1024/1024/1024 AS "CURRNET", sum(memory_size_in_total)/1024/1024/1024 AS "TOTAL" from m_cs_tables

--select INSTANCE_TOTAL_MEMORY_USED_SIZE/1024/1024/1024, INSTANCE_TOTAL_MEMORY_PEAK_USED_SIZE/1024/1024/1024, INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE from SYS.M_HOST_RESOURCE_UTILIZATION

--select to_char(time, 'YYYY-MM-DD'), avg(round(memory_used/memory_size,2)*100) from m_load_history_host group by to_char(time, 'YYYY-MM-DD')

--select to_char(time, 'YYYY-MM-DD'), avg(round(memory_used/memory_allocation_limit,2)*100) from m_load_history_service group by to_char(time, 'YYYY-MM-DD')

--select to_char(time, 'YYYY-MM-DD'), avg(memory_used) from m_load_history_service
--group by to_char(time, 'YYYY-MM-DD')

--select (USED_PHYSICAL_MEMORY + FREE_PHYSICAL_MEMORY)/1024/1024/1024, INSTANCE_TOTAL_MEMORY_USED_SIZE/1024/1024/1024 from SYS.M_HOST_RESOURCE_UTILIZATION

select add_seconds(current_time, 60*30) from dummy

--select * from tables where table_name like '%ALERT%' and schema_name <> 'SAPHANADB'
--select alert_timestamp, alert_rating, alert_host as host, alert_port, alert_details as port from _SYS_STATISTICS.STATISTICS_ALERTS_BASE order by alert_timestamp
--select * from _SYS_STATISTICS.STATISTICS_ALERTS_BASE where to_char(alert_timestamp, 'YYYY-MM-DD HH24:MI') > to_char(add_seconds(current_time, -60*60), 'YYYY-MM-DD HH24:MI')

select to_char(add_seconds(current_timestamp, -60*60), 'YYYY-MM-DD HH24:MI') from dummy

# HEAP Memory가 높은지 Shared Memory가 높은지.. Shared라면 (M_RS_TABLE확인)

SELECT TOP 3 HOST, PORT, SERVICE_NAME,
round(HEAP_MEMORY_USED_SIZE/1024/1024/1024,2) as "HEAP (GB)",
round(SHARED_MEMORY_USED_SIZE/1024/1024/1024,2) as "SHARED (GB)",
round(TOTAL_MEMORY_USED_SIZE/1024/1024/1024,2) as "TOTAL (GB)"
FROM M_SERVICE_MEMORY
ORDER BY SHARED_MEMORY_USED_SIZE DESC

Shared : SELECT TOP 50 * FROM M_RS_TABLES ORDER BY(ALLOCATED_FIXED_PART_SIZE + ALLOCATED_VARIABLE_PART_SIZE) DESC