[HANA] Load 측정 KPI - Load History

Load History

Ivan_Load_History.sql

0.01MB

with raw as (
  select a.timemi
       , a.host
  -- 1. Host
       , a.host_cpu_util
       , a.host_mem_resd_mb
       , a.host_mem_tot_resd_mb
       , a.host_mem_usd_mb
       , a.host_mem_alloc_lim_mb
       , a.host_mem_sz_mb
       , a.host_swap_in_mb_sum 
       , a.host_swap_out_mb_sum
       , a.host_disk_sz_mb
       , a.host_disk_usd_mb
       , a.host_nw_in_mb_sum
       , a.host_nw_out_mb_sum
  -- 2. Service
       , b.cpu_util
       , b.system_cpu_util
       , b.used_mem_mb
       , b.malloc_limmit_mb
       , b.handle_cnt
       , b.ping_tm_ms
       , b.swap_in_mb_sum
  -- 2.1 SQL
       , b.conn_cnt
       , b.int_conn_cnt
       , b.ext_conn_cnt
       , b.idl_conn_cnt
       , b.trans_cnt
       , b.int_trans_cnt
       , b.ext_trans_cnt
       , b.user_trans_cnt
       , b.blocked_trans_cnt
       , b.stmt_cnt
       , b.stmt_per_sec
       , b.mvcc_vers_cnt
       , b.pending_sess_cnt
  -- 2.11 Row Store
       , b.record_lock_cnt
  -- 2.12 Column Store
       , b.cs_rd_cnt
       , b.cs_wr_cnt
       , b.cs_merge_cnt
       , b.cs_unload_cnt
  -- 2.13 Threads
       , b.active_thread_cnt
       , b.wait_thread_cnt
       , b.tot_thread_cnt
       , b.active_sqlexec_cnt
       , b.wait_sqlexec_cnt
       , b.tot_sqlexec_cnt
  -- 2.2 Persistence
       , b.data_wrsz_mb_per_sec
       , b.data_wrtm_sec_per_sec
       , b.log_wrsz_mb_per_sec
       , b.log_wrtm_sec_per_sec
       , b.data_rdsz_mb_per_sec
       , b.data_rdtm_sec_per_sec
       , b.log_rdsz_mb_per_sec
       , b.log_rdtm_sec_per_sec
  -- 2.21 Backup
       , b.databackup_wrsz_mb_per_sec
       , b.databackup_wrtm_sec_per_sec
       , b.logbackup_wrsz_mb_per_sec
       , b.logbackup_wrtm_sec_per_sec
  -- 2.3 Synchronization Primitives
       , b.mutex_collision_cnt
       , b.rdwr_lock_collision_cnt
  -- 2.4 Session Admission Control
       , b.admctl_admit_cnt_sum
       , b.admctl_reject_cnt_sum
       , b.admctl_queue_cnt_sum
       , b.admctl_wait_tm_sec_sum
  from   (
           select host
                , to_varchar(time,'YYYYMMDD-HH24MI')     timemi
                , avg(CPU                    )           host_cpu_util
                , avg(MEMORY_RESIDENT        )/1024/1024 host_mem_resd_mb
                , avg(MEMORY_TOTAL_RESIDENT  )/1024/1024 host_mem_tot_resd_mb
                , avg(MEMORY_USED            )/1024/1024 host_mem_usd_mb
                , avg(MEMORY_ALLOCATION_LIMIT)/1024/1024 host_mem_alloc_lim_mb
                , avg(MEMORY_SIZE            )/1024/1024 host_mem_sz_mb
                , avg(DISK_SIZE              )/1024/1024 host_disk_sz_mb
                , avg(DISK_USED              )/1024/1024 host_disk_usd_mb
                , sum(NETWORK_IN             )/1024/1024 host_nw_in_mb_sum
                , sum(NETWORK_OUT            )/1024/1024 host_nw_out_mb_sum
                , sum(SWAP_IN                )/1024/1024 host_swap_in_mb_sum
                , sum(SWAP_OUT               )/1024/1024 host_swap_out_mb_sum
           from   m_load_history_host
           --from   _sys_statistics.host_load_history_host
           where  time between to_timestamp('&pFROM','YYYYMMDD-HH24MISS') and to_timestamp('&pTO','YYYYMMDD-HH24MISS')
           group  by host, to_varchar(time,'YYYYMMDD-HH24MI')
         ) a inner join (
           select host
                , to_varchar(TIME,'YYYYMMDD-HH24MI')     timemi
                , avg(CPU                       )           cpu_util
                , avg(SYSTEM_CPU                )           system_cpu_util
                , avg(MEMORY_USED               )/1024/1024 used_mem_mb
                , avg(MEMORY_ALLOCATION_LIMIT   )/1024/1024 malloc_limmit_mb
                , avg(HANDLE_COUNT              )           handle_cnt
                , avg(PING_TIME                 )           ping_tm_ms
                , sum(SWAP_IN                   )/1024/1024 swap_in_mb_sum
                , avg(CONNECTION_COUNT          )           conn_cnt
                , avg(INTERNAL_CONNECTION_COUNT )           int_conn_cnt
                , avg(EXTERNAL_CONNECTION_COUNT )           ext_conn_cnt
                , avg(IDLE_CONNECTION_COUNT     )           idl_conn_cnt
                , avg(TRANSACTION_COUNT         )           trans_cnt
                , avg(INTERNAL_TRANSACTION_COUNT)           int_trans_cnt
                , avg(EXTERNAL_TRANSACTION_COUNT)           ext_trans_cnt
                , avg(USER_TRANSACTION_COUNT    )           user_trans_cnt
                , avg(BLOCKED_TRANSACTION_COUNT )           blocked_trans_cnt
                , sum(STATEMENT_COUNT           )           stmt_cnt
                , avg(MVCC_VERSION_COUNT        )           mvcc_vers_cnt
                , avg(PENDING_SESSION_COUNT     )           pending_sess_cnt
                , avg(RECORD_LOCK_COUNT         )           record_lock_cnt
                , avg(CS_MERGE_COUNT            )           cs_merge_cnt
                , avg(CS_READ_COUNT             )           cs_rd_cnt
                , avg(CS_UNLOAD_COUNT           )           cs_unload_cnt
                , avg(CS_WRITE_COUNT            )           cs_wr_cnt
                , avg(ACTIVE_THREAD_COUNT       )           active_thread_cnt
                , avg(WAITING_THREAD_COUNT      )           wait_thread_cnt
                , avg(TOTAL_THREAD_COUNT        )           tot_thread_cnt
                , avg(ACTIVE_SQL_EXECUTOR_COUNT )           active_sqlexec_cnt
                , avg(WAITING_SQL_EXECUTOR_COUNT)           wait_sqlexec_cnt
                , avg(TOTAL_SQL_EXECUTOR_COUNT  )           tot_sqlexec_cnt
                , avg(DATA_WRITE_SIZE/interval_s)/1024/1024 data_wrsz_mb_per_sec
                , avg(DATA_WRITE_TIME/interval_s)/1000000   data_wrtm_sec_per_sec
                , avg(LOG_WRITE_SIZE /interval_s)/1024/1024 log_wrsz_mb_per_sec
                , avg(LOG_WRITE_TIME /interval_s)/1000000   log_wrtm_sec_per_sec
                , avg(DATA_READ_SIZE /interval_s)/1024/1024 data_rdsz_mb_per_sec
                , avg(DATA_READ_TIME /interval_s)/1000000   data_rdtm_sec_per_sec
                , avg(LOG_READ_SIZE  /interval_s)/1024/1024 log_rdsz_mb_per_sec
                , avg(LOG_READ_TIME  /interval_s)/1000000   log_rdtm_sec_per_sec
                , avg(DATA_BACKUP_WRITE_SIZE/interval_s)/1024/1024 databackup_wrsz_mb_per_sec
                , avg(DATA_BACKUP_WRITE_TIME/interval_s)/1000000   databackup_wrtm_sec_per_sec
                , avg(LOG_BACKUP_WRITE_SIZE /interval_s)/1024/1024 logbackup_wrsz_mb_per_sec
                , avg(LOG_BACKUP_WRITE_TIME /interval_s)/1000000   logbackup_wrtm_sec_per_sec
                , avg(MUTEX_COLLISION_COUNT     )            mutex_collision_cnt
                , avg(READ_WRITE_LOCK_COLLISION_COUNT)       rdwr_lock_collision_cnt
                , sum(ADMISSION_CONTROL_ADMIT_COUNT)         admctl_admit_cnt_sum
                , sum(ADMISSION_CONTROL_REJECT_COUNT)        admctl_reject_cnt_sum
                , avg(ADMISSION_CONTROL_QUEUE_SIZE )         admctl_queue_cnt_sum
                , sum(ADMISSION_CONTROL_WAIT_TIME  )/1000000 admctl_wait_tm_sec_sum
                , sum(interval_s                )            interval_sum
                , avg(statement_count/interval_s)            stmt_per_sec
           from   (
                    select a.*, NANO100_BETWEEN(LEAD(TIME, 1) OVER (PARTITION BY HOST, PORT ORDER BY TIME DESC), TIME) / 10000000 INTERVAL_S
                    from   m_load_history_service a 
                    --from   _sys_statistics.host_load_history_service a
                    where  port = 30003
                       and time between to_timestamp('&pFROM','YYYYMMDD-HH24MISS') and to_timestamp('&pTO','YYYYMMDD-HH24MISS')
                  ) a
           group by host, to_varchar(TIME,'YYYYMMDD-HH24MI')
         ) b on a.timemi = b.timemi and a.host = b.host
  order by a.host, a.timemi
)
select *
from   raw
union  all
select 'Min'
     , host
     , min(host_cpu_util        )
     , min(host_mem_resd_mb     )
     , min(host_mem_tot_resd_mb )
     , min(host_mem_usd_mb      )
     , min(host_mem_alloc_lim_mb)
     , min(host_mem_sz_mb       )
     , min(host_swap_in_mb_sum  )
     , min(host_swap_out_mb_sum )
     , min(host_disk_sz_mb      )
     , min(host_disk_usd_mb     )
     , min(host_nw_in_mb_sum    )
     , min(host_nw_out_mb_sum   )
     , min(cpu_util             )
     , min(system_cpu_util      )
     , min(used_mem_mb          )
     , min(malloc_limmit_mb     )
     , min(handle_cnt           )
     , min(ping_tm_ms           )
     , min(swap_in_mb_sum       )
     , min(conn_cnt             )
     , min(int_conn_cnt         )
     , min(ext_conn_cnt         )
     , min(idl_conn_cnt         )
     , min(trans_cnt            )
     , min(int_trans_cnt        )
     , min(ext_trans_cnt        )
     , min(user_trans_cnt       )
     , min(blocked_trans_cnt    )
     , min(stmt_cnt             )
     , min(stmt_per_sec         )
     , min(mvcc_vers_cnt        )
     , min(pending_sess_cnt     )
     , min(record_lock_cnt      )
     , min(cs_rd_cnt            )
     , min(cs_wr_cnt            )
     , min(cs_merge_cnt         )
     , min(cs_unload_cnt        )
     , min(active_thread_cnt    )
     , min(wait_thread_cnt      )
     , min(tot_thread_cnt       )
     , min(active_sqlexec_cnt   )
     , min(wait_sqlexec_cnt     )
     , min(tot_sqlexec_cnt      )
     , min(data_wrsz_mb_per_sec )
     , min(data_wrtm_sec_per_sec)
     , min(log_wrsz_mb_per_sec  )
     , min(log_wrtm_sec_per_sec )
     , min(data_rdsz_mb_per_sec )
     , min(data_rdtm_sec_per_sec)
     , min(log_rdsz_mb_per_sec  )
     , min(log_rdtm_sec_per_sec )
     , min(databackup_wrsz_mb_per_sec )
     , min(databackup_wrtm_sec_per_sec)
     , min(logbackup_wrsz_mb_per_sec  )
     , min(logbackup_wrtm_sec_per_sec )
     , min(mutex_collision_cnt    )
     , min(rdwr_lock_collision_cnt)
     , min(admctl_admit_cnt_sum   )
     , min(admctl_reject_cnt_sum  )
     , min(admctl_queue_cnt_sum   )
     , min(admctl_wait_tm_sec_sum )
from   raw
group  by host
union all
select 'Average'
     , host
     , avg(host_cpu_util        )
     , avg(host_mem_resd_mb     )
     , avg(host_mem_tot_resd_mb )
     , avg(host_mem_usd_mb      )
     , avg(host_mem_alloc_lim_mb)
     , avg(host_mem_sz_mb       )
     , avg(host_swap_in_mb_sum  )
     , avg(host_swap_out_mb_sum )
     , avg(host_disk_sz_mb      )
     , avg(host_disk_usd_mb     )
     , avg(host_nw_in_mb_sum    )
     , avg(host_nw_out_mb_sum   )
     , avg(cpu_util             )
     , avg(system_cpu_util      )
     , avg(used_mem_mb          )
     , avg(malloc_limmit_mb     )
     , avg(handle_cnt           )
     , avg(ping_tm_ms           )
     , avg(swap_in_mb_sum       )
     , avg(conn_cnt             )
     , avg(int_conn_cnt         )
     , avg(ext_conn_cnt         )
     , avg(idl_conn_cnt         )
     , avg(trans_cnt            )
     , avg(int_trans_cnt        )
     , avg(ext_trans_cnt        )
     , avg(user_trans_cnt       )
     , avg(blocked_trans_cnt    )
     , avg(stmt_cnt             )
     , avg(stmt_per_sec         )
     , avg(mvcc_vers_cnt        )
     , avg(pending_sess_cnt     )
     , avg(record_lock_cnt      )
     , avg(cs_rd_cnt            )
     , avg(cs_wr_cnt            )
     , avg(cs_merge_cnt         )
     , avg(cs_unload_cnt        )
     , avg(active_thread_cnt    )
     , avg(wait_thread_cnt      )
     , avg(tot_thread_cnt       )
     , avg(active_sqlexec_cnt   )
     , avg(wait_sqlexec_cnt     )
     , avg(tot_sqlexec_cnt      )
     , avg(data_wrsz_mb_per_sec )
     , avg(data_wrtm_sec_per_sec)
     , avg(log_wrsz_mb_per_sec  )
     , avg(log_wrtm_sec_per_sec )
     , avg(data_rdsz_mb_per_sec )
     , avg(data_rdtm_sec_per_sec)
     , avg(log_rdsz_mb_per_sec  )
     , avg(log_rdtm_sec_per_sec )
     , avg(databackup_wrsz_mb_per_sec )
     , avg(databackup_wrtm_sec_per_sec)
     , avg(logbackup_wrsz_mb_per_sec  )
     , avg(logbackup_wrtm_sec_per_sec )
     , avg(mutex_collision_cnt    )
     , avg(rdwr_lock_collision_cnt)
     , avg(admctl_admit_cnt_sum   )
     , avg(admctl_reject_cnt_sum  )
     , avg(admctl_queue_cnt_sum   )
     , avg(admctl_wait_tm_sec_sum )
from   raw
group  by host
union all
select 'Max'
     , host
     , max(host_cpu_util        )
     , max(host_mem_resd_mb     )
     , max(host_mem_tot_resd_mb )
     , max(host_mem_usd_mb      )
     , max(host_mem_alloc_lim_mb)
     , max(host_mem_sz_mb       )
     , max(host_swap_in_mb_sum  )
     , max(host_swap_out_mb_sum )
     , max(host_disk_sz_mb      )
     , max(host_disk_usd_mb     )
     , max(host_nw_in_mb_sum    )
     , max(host_nw_out_mb_sum   )
     , max(cpu_util             )
     , max(system_cpu_util      )
     , max(used_mem_mb          )
     , max(malloc_limmit_mb     )
     , max(handle_cnt           )
     , max(ping_tm_ms           )
     , max(swap_in_mb_sum       )
     , max(conn_cnt             )
     , max(int_conn_cnt         )
     , max(ext_conn_cnt         )
     , max(idl_conn_cnt         )
     , max(trans_cnt            )
     , max(int_trans_cnt        )
     , max(ext_trans_cnt        )
     , max(user_trans_cnt       )
     , max(blocked_trans_cnt    )
     , max(stmt_cnt             )
     , max(stmt_per_sec         )
     , max(mvcc_vers_cnt        )
     , max(pending_sess_cnt     )
     , max(record_lock_cnt      )
     , max(cs_rd_cnt            )
     , max(cs_wr_cnt            )
     , max(cs_merge_cnt         )
     , max(cs_unload_cnt        )
     , max(active_thread_cnt    )
     , max(wait_thread_cnt      )
     , max(tot_thread_cnt       )
     , max(active_sqlexec_cnt   )
     , max(wait_sqlexec_cnt     )
     , max(tot_sqlexec_cnt      )
     , max(data_wrsz_mb_per_sec )
     , max(data_wrtm_sec_per_sec)
     , max(log_wrsz_mb_per_sec  )
     , max(log_wrtm_sec_per_sec )
     , max(data_rdsz_mb_per_sec )
     , max(data_rdtm_sec_per_sec)
     , max(log_rdsz_mb_per_sec  )
     , max(log_rdtm_sec_per_sec )
     , max(databackup_wrsz_mb_per_sec )
     , max(databackup_wrtm_sec_per_sec)
     , max(logbackup_wrsz_mb_per_sec  )
     , max(logbackup_wrtm_sec_per_sec )
     , max(mutex_collision_cnt    )
     , max(rdwr_lock_collision_cnt)
     , max(admctl_admit_cnt_sum   )
     , max(admctl_reject_cnt_sum  )
     , max(admctl_queue_cnt_sum   )
     , max(admctl_wait_tm_sec_sum )
from   raw
group  by host
;

- 해당 Load의 History 결과를 시간별로 보여주며 
  마지막에 Min, Max, Average 값을 보여줌

 

LOAD INFO