Мониторинг нагрузки и особенности индексирования Sap Hana

Предоставления для мониторинга нагрузки на субд sap hana:

SAP HANA

Представлю основные запросы для мониторинга нагрузки на sap hana в разрезе времени (что то похожее на awr отчеты в oracle)

1. Использование CPU, Озу и физической памяти в разрезе 10 минут:

select concat( SUBSTRING (TO_NVARCHAR(SERVER_TIMESTAMP, 'yyyy.mm.dd hh24:mi'),1,15), '0') as hh,
round(100*SUM(TOTAL_CPU_USER_TIME_DELTA) / SUM(SNAPSHOT_DELTA) ,2) as cpu_prc,
round(AVG(INSTANCE_TOTAL_MEMORY_USED_SIZE)/1024/1024/1024,2) MEMORY_USED,
round(AVG(INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE)/1024/1024/1024,2) MEMORY_ALLOCATED,
round(100*SUM(INSTANCE_TOTAL_MEMORY_USED_SIZE) / ( SUM(INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE) ),2) MEMORY_USED_PRC,
round(AVG(FREE_PHYSICAL_MEMORY)/1024/1024/1024,2) FREE_PHYSICAL_MEMORY,
round(AVG(USED_PHYSICAL_MEMORY)/1024/1024/1024,2) USED_PHYSICAL_MEMORY,
round(100*SUM(USED_PHYSICAL_MEMORY) / ( SUM(FREE_PHYSICAL_MEMORY)+SUM(USED_PHYSICAL_MEMORY) ),2) USED_PHYSICAL_MEMORY_PRC,
round(AVG(FREE_SWAP_SPACE)/1024/1024/1024,2) FREE_SWAP_SPACE,
round(AVG(USED_SWAP_SPACE)/1024/1024/1024,2) USED_SWAP,
round(100*SUM(USED_SWAP_SPACE) / ( SUM(FREE_SWAP_SPACE)+SUM(USED_SWAP_SPACE) ),2) USED_SWAP_PRC
from _SYS_STATISTICS.HOST_RESOURCE_UTILIZATION_STATISTICS
where TO_NVARCHAR(SERVER_TIMESTAMP, 'yyyymmddhh24mi') between '2017081410' and '201708142359'
group by SUBSTRING (TO_NVARCHAR(SERVER_TIMESTAMP, 'yyyy.mm.dd hh24:mi'),1,15)
order by SUBSTRING (TO_NVARCHAR(SERVER_TIMESTAMP, 'yyyy.mm.dd hh24:mi'),1,15) desc

В графическом виде это будет выглядеть так:


2. Интенсивность чтения и записи:

select concat( SUBSTRING (TO_NVARCHAR(SERVER_TIMESTAMP, 'yyyy.mm.dd hh24:mi'),1,15), '0') as hh,
SUM(TOTAL_IO_TIME_DELTA)/1000/1000 as io_sec, SUM(TOTAL_READ_SIZE_DELTA) read_size, SUM(TOTAL_READ_TIME_DELTA)/1000/1000 as read_sec,
SUM(TOTAL_WRITE_SIZE_DELTA) as write_size, SUM(TOTAL_WRITE_TIME_DELTA/1000/1000) as write_sec,
SUM(TOTAL_FAILED_READS_DELTA) as failed_reads, SUM(TOTAL_FAILED_WRITES_DELTA) as failed_writes
from _SYS_STATISTICS.HOST_VOLUME_IO_TOTAL_STATISTICS
where TO_NVARCHAR(SERVER_TIMESTAMP, 'yyyymmddhh24mi') between '2017081410' and '201708142359'
group by SUBSTRING (TO_NVARCHAR(SERVER_TIMESTAMP, 'yyyy.mm.dd hh24:mi'),1,15)
order by SUBSTRING (TO_NVARCHAR(SERVER_TIMESTAMP, 'yyyy.mm.dd hh24:mi'),1,15) desc

В графическом виде это будет выглядеть так:
Время чтения в 10 минут:

Размер чтений в 10 минут:


3. Топовые sql запросы по времени выполнения:

select * from (
       select v.* , ROW_NUMBER() OVER(pARTITION BY hh ORDER BY DELTA_TIME desc) as rn
       from (
             select TO_NVARCHAR(SERVER_TIMESTAMP, 'yyyy.mm.dd hh24') as hh,
             AVG(AVG_EXECUTION_TIME)/1000/1000, SUM(EXECUTION_COUNT),
             AVG(AVG_EXECUTION_TIME)/1000/1000*SUM(EXECUTION_COUNT) as DELTA_TIME,
             STRING_AGG(USER_NAME), TO_VARCHAR(STATEMENT_STRING) as txt, MAX(index) as index
             from _SYS_STATISTICS.HOST_SQL_PLAN_CACHE
             where SERVER_TIMESTAMP between to_date('01.08.2017', 'dd.mm.yyyy') and to_date('02.08.2017', 'dd.mm.yyyy')
             group by TO_NVARCHAR(SERVER_TIMESTAMP, 'yyyy.mm.dd hh24'), TO_VARCHAR(STATEMENT_STRING)
       ) v
)
where rn <= 5
order by hh, rn;

Получится что-то вроде такого топа:



Детализация продолжительных запросов до объема потребляемого озу, cpu и блокировок (HOST_SQL_PLAN_CACHE этой информации не содержит):

select STATEMENT_ID, WORKLOAD_CLASS_NAME, STATEMENT_MEMORY_LIMIT,  APP_USER, START_TIME, DURATION_MICROSEC, RECORDS,
LOCK_WAIT_DURATION, MEMORY_SIZE/1024/1024/1024 as SIZE_GB, CPU_TIME, ERROR_TEXT, STATEMENT_STRING
from M_EXPENSIVE_STATEMENTS 
where START_TIME between to_date('11.11.2020 11', 'dd.mm.yyyy hh24') and to_date('12.11.2020', 'dd.mm.yyyy')
and STATEMENT_STRING like '%ASSORT_TAIL_RATING_CV01%'
order by START_TIME desc

По этому представлению можно также получить ошибку, если запрос умер по out of memory и любой другой причине

4. Детализация событий конкретного запроса:
Что то похожее на ash в оракле, но без привязки к конкретной строке плана:

select APPLICATION_NAME, APPLICATION_USER_NAME, CLIENT_IP, USER_NAME,
LOCKS_OWNED, STATEMENT_HASH, TRANSACTION_ID,
THREAD_ID, THREAD_METHOD, THREAD_STATE, THREAD_TYPE, 
CPU_TIME_CUMULATIVE, CPU_TIME_SELF, DURATION, HIERARCHY,
THREAD_DETAIL
from _SYS_STATISTICS.HOST_SERVICE_THREAD_SAMPLES 
where timestamp between TO_TIMESTAMP('2020/06/04 14:10:00', 'YYYY/MM/DD HH24:MI:SS') and 
TO_TIMESTAMP('2020/06/04 14:50:00', 'YYYY/MM/DD HH24:MI:SS') 
AND STATEMENT_HASH = '56c95f965372d89dd904f30075fa51b3'
order by timestamp;

Другие разрезы времени:
SYS.M_SERVICE_THREADS - сейчас работающий прогарммы
SYS.M_SERVICE_THREAD_SAMPLES - последние 2 часа работы
_SYS_STATISTICS.HOST_SERVICE_THREAD_SAMPLES - долгое хранилище истории с агрегацией до 10 минут
_SYS_STATISTICS.HOST_SERVICE_THREAD_SAMPLES_STATEMENTS - история программ и запросов
детальней о типах событий в ноте: 2114710 - FAQ: SAP HANA Threads and Thread Samples


5. Использование сети с момента сброса (system reset):

ALTER SYSTEM RESET MONITORING VIEW SYS. M_SERVICE_NETWORK_IO_RESET;
select SENDER_HOST, RECEIVER_HOST, sum(SEND_SIZE), sum(RECEIVE_SIZE), sum(SEND_DURATION)/1000/1000, sum(RECEIVE_DURATION)/1000/1000, sum(REQUEST_COUNT)
from SYS.M_SERVICE_NETWORK_IO_RESET Group BY SENDER_HOST, RECEIVER_HOST

6. Время, объем и причина выполнения Delta Merge:
Полезно для анализа медленно работающих dml

select  start_time, Host, port, table_name, part_id, execution_time, merged_delta_records, memory_merge, motivation, LAST_ERROR
from _sys_statistics.host_delta_merge_statistics  where success = 'TRUE' and type= 'MERGE'
AND table_name = 'ZTGM_CONNECT'
AND start_time between '2020021013' and '2020021014'

7. Фиксация хинта для запроса в кэше

ALTER SYSTEM PIN SQL PLAN CACHE ENTRY <plan_id> WITH HINT ( <hint_items> ) 

8. Макросы в строенном скриптовом языке:

BEGIN
 tab = SELECT A, B, C
 FROM T WHERE A = 1;
 tab2 = SELECT C FROM :tab WHERE C = 0;
END;

что преобразуется в запрос

WITH "_SYS_TAB_2" AS (SELECT A, B, C FROM T WHERE A = 1) 
SELECT C FROM "_SYS_TAB_2"
WHERE C = 0

9. Типы индексов:
* BTREE - обычный и сжатый по префикксу для rowstore таблиц

Индексы для колоночных таблиц создаются как на main storage, так и над delta
Все колонки содержат сортированные словари с уникальными значениями, но нет маппингов на строки (левая часть изобраения)

* inverted (individual) index - добавляет маппинг словаря на строки таблицы (правая часть изображения)
* inverted concat (value) index - на колонках индекса создается новое конкатенирующее поле в таблице. Индекс создается над этим виртуальным столбцом
* inverted hash index - в сортированном справочнике и в самой таблице сохраняются хэши конкатенации строк, а не сами значения, что уменьшает размер памяти под них

Если PK состоит из нескольих полей, то создается общий inverted concat (value) index, а так же на каждое поле отдельно inverted (individual) index (SAP_HANA_Performance_Developer_Guide - 10 или 12 страница)

Т.е. все индексы, кроме individual, увеличивает расход памяти и замедляет вставки (M_CS_COLUMNS - информация о размере и сжатии колонок и индексов)

10. Влияние партицирование на расход памяти:
* может снизить расходы на справочники и улучшить сжатие, если партиции разделяют данные на неповторяющиеся части.
к примеру партицировать по полу пола: в 1 партицию попадут М, в другую Ж
* так и увеличить потребление памяти, если колонка будет содержать одинаковые уникальные значения в обоих партциях

MSSQL

топ sql запросов в разрезе показателей с последней перезагрузки бд:

SELECT  TOP 20 creation_time 
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads 
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count / 1000 avg_elapsed_time_ms
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text,
   qp.query_plan,
   st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time desc;