[HANA] Backlog관련 진행현황 조회 쿼리 작성 및 유의사항

- 목표 : Redo log 발생량과 Replay 현황 조회

 

- 내용 
  . Primary : Resource 현황 (CPU Utilization, Used Memory)
                DB Throughput (TPS, Stmt/sec)
                Log 현황 (Log Read/Write size and time)
  . Secondary : Backlog 현황 (Size and time)
                    Replay backlog 현황 (Size and time)

 

 

- 항목별 Sub-query 생성

  . Primary-Resource (CPU) - HOST_SERVICE_STATISTICS (42days / 60 seconds)

    > SERVER_TIMESTAMP 주기는 60 초 => 60초 단위 데이터 존재

 

 

  . Primary-Resource (Memory) - HOST_LOAD_HISTORY_HOST (42 days / 3600 seconds)

    > SERVER_TIMESTAMP 주기는 3600초 (=1시간), TIME 주기는 약 10초 => 즉, 10초 단위의 데이터 존재함

 

 

  . Primary - DB Throughput (TPS, Stmt/sec) - HOST_WORKLOAD_BASE (42 days / 60 seconds)

    > SERVER_TIMESTAMP 주기는 60 초 => 60초 단위 데이터 존재  
    > CURRENT_EXECUTION_RATE 는 분단위 데이터로 초단위로 변경을 위해서는 /60 필요

 

 

  . Primary - Log 현황 - HOST_LOAD_HISTORY_SERVICE (42 days / 3600 seconds)

    > SERVER_TIMESTAMP 주기는 3600초 (=1시간), TIME 주기는 약 10초 => 즉, 10초 단위의 데이터 존재함

 

 

  . Secondary - Replication 현황 (Backlog 등) - HOST_SERVICE_REPLICATION (42 days / 3600 seconds)

    > SERVER_TIMESTAMP 주기는 3600초 (=1시간) => 주기를 1분으로 조정

 

 

 

 

- 고려사항 

  1. 각 View별 수집 주기가 다름 -> 동일한 x축 (시간)으로 조정 필요

    > select to_varchar(SERVER_TIMESTAMP, 'YYYYMMDD-HH24MI') tm ~~~
       group by to_varchar(SERVER_TIMESTAMP, 'YYYYMMDD-HH24MI')

 

    > 항목에 따라서 sum / avg 선택 필요
       : SUM - HOST_WORKLOAD_BASE.EXECUTION_COUNT - 해당 시간구간 동안의 모든 Excution 수를 더해야 함
       : AVG - HOST_WORKLOAD_BASE.CURRENT_EXECUTION_RATE - 분당 Execution 수 값이므로 평균값 필요
    

 

  2. 각 View의 항목별 단위가 다름 
    > Time : us, ms, sec 등 
    > Size : bytes, Kbytes, Mbytes 등

 

    > 시간당 처리량 : TPS (Transaction Per Sec) vs TPM (Transaction Per Min)
       : HOST_WORKLOAD_BASE.CURRENT_EXECUTION_RATE / 60 - 분당 Exec 수 -> 초당 Exec 수로 변경

 

  3. 1 Row에서 확인하기 위해서 Host별 결과값을 정비
    > case when host = 'dbsrv01' then EXECUTION_COUNT end db1_exec_cnt,
       case when host = 'dbsrv02' then EXECUTION_COUNT end db2_exec_cnt,
       case when host = 'dbsrv03' then EXECUTION_COUNT end db3_exec_cnt,          
       ~~~

   

 

- 상기 내용을 모두 적용한 Query 문 (Sample)

with tt as (     select distinct to_varchar(SERVER_TIMESTAMP,'YYYYMMDD-HH24MI') tm     from   _sys_statistics.host_service_statistics_base     where  SERVER_TIMESTAMP            between to_timestamp('20200618-150000','YYYYMMDD-HH24MISS') and to_timestamp('20200618-235900','YYYYMMDD-HH24MISS') ) select a.tm      , r1_cpu, r1_mem_used_gb, r1_Exec  , r1_CurrExec_per_sec, r1_Trans_cnt, r1_Curr_TPS, r1_log_rdsz_mb, r1_log_rdtm_sec, r1_log_wrsz_mb, r1_log_wrtm_sec      , r2_cpu, r2_mem_used_gb, r2_Exec  , r2_CurrExec_per_sec, r2_Trans_cnt, r2_Curr_TPS, r2_log_rdsz_mb, r2_log_rdtm_sec, r2_log_wrsz_mb, r2_log_wrtm_sec      , r3_blog_tm_ms, r3_blog_sz_mb, r3_rep_blog_tm_ms, r3_rep_blog_sz_mb, r3_log_rdsz_mb, r3_log_rdtm_sec, r3_log_wrsz_mb, r3_log_wrtm_sec      , r4_blog_tm_ms, r4_blog_sz_mb, r4_rep_blog_tm_ms, r4_rep_blog_sz_mb, r4_log_rdsz_mb, r4_log_rdtm_sec, r4_log_wrsz_mb, r4_log_wrtm_sec      , r5_blog_tm_ms, r5_blog_sz_mb, r5_rep_blog_tm_ms, r5_rep_blog_sz_mb, r5_log_rdsz_mb, r5_log_rdtm_sec, r5_log_wrsz_mb, r5_log_wrtm_sec      , r6_blog_tm_ms, r6_blog_sz_mb, r6_rep_blog_tm_ms, r6_rep_blog_sz_mb, r6_log_rdsz_mb, r6_log_rdtm_sec, r6_log_wrsz_mb, r6_log_wrtm_sec from   (          select to_varchar(SERVER_TIMESTAMP,'YYYYMMDD-HH24MI') tm               , sum(case when host = 'dbsrv01' then EXECUTION_COUNT               end) r1_Exec               , avg(case when host = 'dbsrv01' then CURRENT_EXECUTION_RATE/60     end) r1_CurrExec_per_sec               , sum(case when host = 'dbsrv01' then COMMIT_COUNT + ROLLBACK_COUNT end) r1_Trans_cnt               , avg(case when host = 'dbsrv01' then CURRENT_TRANSACTION_RATE/60   end) r1_Curr_TPS               , sum(case when host = 'dbsrv02' then EXECUTION_COUNT               end) r2_Exec               , avg(case when host = 'dbsrv02' then CURRENT_EXECUTION_RATE        end) r2_CurrExec_per_sec               , sum(case when host = 'dbsrv02' then COMMIT_COUNT + ROLLBACK_COUNT end) r2_Trans_cnt               , avg(case when host = 'dbsrv02' then CURRENT_TRANSACTION_RATE      end) r2_Curr_TPS          from   _sys_statistics.host_workload_base a, tt          where  left(to_varchar(SERVER_TIMESTAMP,'YYYYMMDD-HH24MI'),12)           and port = 30003          group  by left(to_varchar(SERVER_TIMESTAMP,'YYYYMMDD-HH24MI'),12)         ) a      , (          select to_varchar(SERVER_TIMESTAMP,'YYYYMMDD-HH24MI') tm               , avg(case when secondary_host = 'dbsrv03' then BACKLOG_TIME       /1000      end) r3_blog_tm_ms               , avg(case when secondary_host = 'dbsrv03' then BACKLOG_SIZE       /1024/1024 end) r3_blog_sz_mb               , avg(case when secondary_host = 'dbsrv03' then REPLAY_BACKLOG_TIME/1000      end) r3_rep_blog_tm_ms               , avg(case when secondary_host = 'dbsrv03' then REPLAY_BACKLOG_SIZE/1024/1024 end) r3_rep_blog_sz_mb               , avg(case when secondary_host = 'dbsrv04' then BACKLOG_TIME       /1000      end) r4_blog_tm_ms               , avg(case when secondary_host = 'dbsrv04' then BACKLOG_SIZE       /1024/1024 end) r4_blog_sz_mb               , avg(case when secondary_host = 'dbsrv04' then REPLAY_BACKLOG_TIME/1000      end) r4_rep_blog_tm_ms               , avg(case when secondary_host = 'dbsrv04' then REPLAY_BACKLOG_SIZE/1024/1024 end) r4_rep_blog_sz_mb               , avg(case when secondary_host = 'dbsrv05' then BACKLOG_TIME       /1000      end) r5_blog_tm_ms               , avg(case when secondary_host = 'dbsrv05' then BACKLOG_SIZE       /1024/1024 end) r5_blog_sz_mb               , avg(case when secondary_host = 'dbsrv05' then REPLAY_BACKLOG_TIME/1000      end) r5_rep_blog_tm_ms               , avg(case when secondary_host = 'dbsrv05' then REPLAY_BACKLOG_SIZE/1024/1024 end) r5_rep_blog_sz_mb               , avg(case when secondary_host = 'dbsrv06' then BACKLOG_TIME       /1000      end) r6_blog_tm_ms               , avg(case when secondary_host = 'dbsrv06' then BACKLOG_SIZE       /1024/1024 end) r6_blog_sz_mb               , avg(case when secondary_host = 'dbsrv06' then REPLAY_BACKLOG_TIME/1000      end) r6_rep_blog_tm_ms               , avg(case when secondary_host = 'dbsrv06' then REPLAY_BACKLOG_SIZE/1024/1024 end) r6_rep_blog_sz_mb          from   _sys_statistics.host_service_replication, tt          where  left(to_varchar(SERVER_TIMESTAMP,'YYYYMMDD-HH24MI'),12)              and port = 30003          group by left(to_varchar(SERVER_TIMESTAMP,'YYYYMMDD-HH24MI'),12)                       order by 1        ) b      , (          select to_varchar(SERVER_TIMESTAMP,'YYYYMMDD-HH24MI') tm               , avg(case when host = 'dbsrv01' then TOTAL_CPU end) r1_cpu               , avg(case when host = 'dbsrv02' then TOTAL_CPU end) r2_cpu               , avg(case when host = 'dbsrv03' then TOTAL_CPU end) r3_cpu               , avg(case when host = 'dbsrv04' then TOTAL_CPU end) r4_cpu               , avg(case when host = 'dbsrv05' then TOTAL_CPU end) r5_cpu               , avg(case when host = 'dbsrv06' then TOTAL_CPU end) r6_cpu          from   _sys_statistics.host_service_statistics_base, tt          where  left(to_varchar(SERVER_TIMESTAMP,'YYYYMMDD-HH24MI'),12)           group by left(to_varchar(SERVER_TIMESTAMP,'YYYYMMDD-HH24MI'),12)         ) c      , (          select to_varchar(TIME,'YYYYMMDD-HH24MI') tm               , avg(case when host = 'dbsrv01' then MEMORY_USED/1024/1024/1024 end) r1_mem_used_gb               , avg(case when host = 'dbsrv02' then MEMORY_USED/1024/1024/1024 end) r2_mem_used_gb               , avg(case when host = 'dbsrv03' then MEMORY_USED/1024/1024/1024 end) r3_mem_used_gb               , avg(case when host = 'dbsrv04' then MEMORY_USED/1024/1024/1024 end) r4_mem_used_gb               , avg(case when host = 'dbsrv05' then MEMORY_USED/1024/1024/1024 end) r5_mem_used_gb               , avg(case when host = 'dbsrv06' then MEMORY_USED/1024/1024/1024 end) r6_mem_used_gb          from   _sys_statistics.host_load_history_host, tt          where  left(to_varchar(TIME,'YYYYMMDD-HH24MI'),12)  　          group  by left(to_varchar(TIME,'YYYYMMDD-HH24MI'),12)         ) d      , (          select to_varchar(TIME,'YYYYMMDD-HH24MI') tm               , sum(case when host = 'dbsrv01' then LOG_READ_SIZE /1024/1024/1024 end) r1_log_rdsz_mb               , sum(case when host = 'dbsrv01' then LOG_READ_TIME /1000000        end) r1_log_rdtm_sec               , sum(case when host = 'dbsrv01' then LOG_WRITE_SIZE/1024/1024/1024 end) r1_log_wrsz_mb               , sum(case when host = 'dbsrv01' then LOG_WRITE_TIME/1000000        end) r1_log_wrtm_sec               , sum(case when host = 'dbsrv02' then LOG_READ_SIZE /1024/1024/1024 end) r2_log_rdsz_mb               , sum(case when host = 'dbsrv02' then LOG_READ_TIME /1000000        end) r2_log_rdtm_sec               , sum(case when host = 'dbsrv02' then LOG_WRITE_SIZE/1024/1024/1024 end) r2_log_wrsz_mb               , sum(case when host = 'dbsrv02' then LOG_WRITE_TIME/1000000        end) r2_log_wrtm_sec               , sum(case when host = 'dbsrv03' then LOG_READ_SIZE /1024/1024/1024 end) r3_log_rdsz_mb               , sum(case when host = 'dbsrv03' then LOG_READ_TIME /1000000        end) r3_log_rdtm_sec               , sum(case when host = 'dbsrv03' then LOG_WRITE_SIZE/1024/1024/1024 end) r3_log_wrsz_mb               , sum(case when host = 'dbsrv03' then LOG_WRITE_TIME/1000000        end) r3_log_wrtm_sec               , sum(case when host = 'dbsrv04' then LOG_READ_SIZE /1024/1024/1024 end) r4_log_rdsz_mb               , sum(case when host = 'dbsrv04' then LOG_READ_TIME /1000000        end) r4_log_rdtm_sec               , sum(case when host = 'dbsrv04' then LOG_WRITE_SIZE/1024/1024/1024 end) r4_log_wrsz_mb               , sum(case when host = 'dbsrv04' then LOG_WRITE_TIME/1000000        end) r4_log_wrtm_sec               , sum(case when host = 'dbsrv05' then LOG_READ_SIZE /1024/1024/1024 end) r5_log_rdsz_mb               , sum(case when host = 'dbsrv05' then LOG_READ_TIME /1000000        end) r5_log_rdtm_sec               , sum(case when host = 'dbsrv05' then LOG_WRITE_SIZE/1024/1024/1024 end) r5_log_wrsz_mb               , sum(case when host = 'dbsrv05' then LOG_WRITE_TIME/1000000        end) r5_log_wrtm_sec               , sum(case when host = 'dbsrv06' then LOG_READ_SIZE /1024/1024/1024 end) r6_log_rdsz_mb               , sum(case when host = 'dbsrv06' then LOG_READ_TIME /1000000        end) r6_log_rdtm_sec               , sum(case when host = 'dbsrv06' then LOG_WRITE_SIZE/1024/1024/1024 end) r6_log_wrsz_mb               , sum(case when host = 'dbsrv06' then LOG_WRITE_TIME/1000000        end) r6_log_wrtm_sec          from _sys_statistics.host_load_history_service_base a, tt          where  left(to_varchar(TIME,'YYYYMMDD-HH24MI'),12)           group by left(to_varchar(TIME,'YYYYMMDD-HH24MI'),12)         ) e where  a.tm = b.tm    and a.tm = c.tm    and a.tm = d.tm    and a.tm = e.tm order  by 1

  . with 구문 : timestamp 구간 변경을 한군데에서만 수정하기 위한 꼼수(?)